skip to Main Content

Guest Post by Willem van Dijk

Intro

I am, like most (if not all) visitors to this website, an addict for information –> data –> truth –> Εμπειρία <-hope that sounds like Greek to you…

My journey into Power Pivot has been one of uphill struggles and downhill thrills. It’s been brief moments of ego feeding, immediately followed by lengthy bouts of meekness. One moment I am Indiana Jones going for his gun in The Raiders of the Lost Ark directly followed by the same scene in The Temple of Doom


Where did my DAX go?

Though I’d like to view myself as an Excel Power User, I often refer to myself as an Excel Power (ab)User 🙂 Lacking any formal training in computer anything, it has been a feline curiosity which introduced me to =SUBTOTAL(), =VLOOKUP(), PivotTables, combination of =IF() =VLOOKUP() and PivotTables, to viewing my laptop blowing up while trying to create a report for our corporate execs allowing one to drill down to any order / invoicing line in our system.

The report afore mentioned, was labeled DEFCON4 with a deadline of “I need it two minutes ago”, hence forth known as INIT2MA <—please feel free to use this as a trusted password.
With what little time I had left there was only one source I could trust to help me out: GOOGLE
The search string I entered shall remain a mystery, but probably looked something like

Alright, I fibbed… Somewhere in that search string I probably/accidently/fortuitously wrote the words “PivotTable”, “massive data” and “power”. which lead me to two sites: Microsoft.com and the one you are reading right now.

* Power Pivot enters stage right
* hit spotlight
* dim stage light
* cue music
* cue smoke

Oh, I forgot to mention *trial and error and several *what the heck’s.
2 days later, I had the report I needed. That’s right, it took me, unqualified dimwit, 2 days to learn how to set-up my base data, write several measures and create a lovely 3 sheet report with graphs and slicers and all that’s filled with sugar and spicers… Had Rob’s blogs not been so fun to read it probably would have taken me less time, but he sidetracked me with his wit!

Okay, so about that FIRSTDATE()…

FIRSTDATE()

About a week ago I was updating a dashboard in which, amongst other things, there is a graph with a constant moving TTM graph (TTM being trailing 12 months.)
A graph which is very useful to show historic growth, should there be growth, obviously. There’s just one little, teenie-weenie problem with the graph, which is that the first and last month reported are never FULL months. To make matters more fun, in my industry we tend to be top-heavy the last week of the month—you know, hit the target, sell your grandmother and get a Espresso machine to boot…

Of all the benefits which PowerPivot has to offer, it is time-intelligence which I (ab)use the most, it is also the one which I tend to understand the least and it’s also the one which is lacking in standard commands to make (my) life easier: PREVIOUSPERIODX() where is X = Hour, Days, Weeks, Months, Quarters or Years

So back to GOOGLE I went… and it brought me to fellow PowerPivotPro blogger Kasper de Jong’s website where I had found the formulaic joy I was looking for—the below stolen straight from his blog post and edited to remove portion not needed today.

=CALCULATE(SUM(FactInventory[DaysInStock]),
  DATESBETWEEN(DimDate[Datekey],
  FIRSTDATE(DATEADD(DimDate[Datekey],-5,MONTH)),
  LASTDATE(DimDate[Datekey])))

The key I was looking for was the DATESBETWEEN() portion. I adjusted and tweaked here and there due to the obvious different table names I use as well as the different time frame I was looking for (-13, MONTH) and hit the ENTER key… and no joy.

Why?
DATESBETWEEN() is, in essence, a great tool, if one understands the logic behind it all which is as simple as as adding 1 + 1 to get to 2
FIRSTDATE() is the start date—say, Aug 1st 2013
LASTDATE() is the end date—say, Jul 31st 2014
Now, should my dashboard be one which is static, only to be used ones, I can easily type

=CALCULATE([NetSales],
  DATESINPERIOD(DimDate[Datekey],
  DATE(2014,7,31),
  -1,YEAR))

But my dashboard is not static and, to make matters worse, I am lazy and can’t be bothered to manually alter things in a dashboard every time the data has been refreshed, kind of defeats the purpose of automation, doesn’t it?

The issue I had with Kasper’s help was the DATEADD() portion which I applied to both FIRSTDATE() as well as LASTDATE() where, according to the logic, I would get the first day of 13 months ago as well as the last day of 1 month ago essentially giving me a nice TTM

Below a PivotTable showing 2 measures, one being my original TTM, the other being my new DATESBETWEEN() which should show me previous 12 months of invoicing

WOW! or, as the French would say “WOWEE?”
It seems that if I want to cook the sales books I don’t need to do much…
FYI, here are the final two measures used for the above

TTMNetUSDchart:=CALCULATE([NetSales],
DATESBETWEEN(‘Date Values’October 9, 2014,
TODAY()-365,TODAY()),
‘Date Values’)

TTMNetUSDchart3:=CALCULATE([NetSales],
DATESBETWEEN(‘Date Values’October 9, 2014,
FIRSTDATE(DATEADD(‘Date Values’October 9, 2014,-12,month)),
LASTDATE(DATEADD(‘Date Values’October 9, 2014,-1,month))))

To check part of the issue, I went and just wrote the formula portions of FIRSTDATE and LASTDATE to see what it would give me

Well, that’s not right, or is it? My ‘Date Values’ is limited to a period from Jan 1st 2012 through Dec 31st 2015, thus we can deduce that we were able to subtract 1 month from the last date of my calendar table, but that isn’t going to help me at all… I know, what if I change the FIRSTDATE() and LASTDATE() to look at my invoicing dates?

Contiguous date you say? well, drattttttt!!!!!!!!!!!!!!!!! or as the French would say “Of course, you silly man, you should have seen that one coming kilometers away…”

So, we know that we want to apply DATESBETWEEN(), we just can’t figure out how. Fear not, the cavalry heard my call for help and jumped into action.

Solution

The solution to my problem was, from the get-go, semantics. As a matter of fact, semantics is what in general will solve any issue one might have with DAX.
My problem is not the lack of DAX knowledge, I can speak DAX, but can I speak to DAX? Will DAX listen to me when I tell it that all I want for Christmas is a nice graph showing the previous full 12 months worth of sales? In other words, how do I solve my DATESBETWEEN() issue when FIRSTDATE() and LASTDATE() don’t seem to work?

Solution: 2 new measures

first date ttm:=EOMONTH(TODAY(),-13)+1

Last day of previous month:=EOMONTH(TODAY(),-1)

EOMONTH() essentially gives us the last date of a month, where we can specify which month we want to look at. Thus, the measure ‘first date ttm’ says give me the last date of 13 months ago, and add 1 day which will give me the first date I need. Conversely, ‘Last day of previous month’ will give me the last day of the previous month…

The final measure for my chart looks like this

TTMNetUSDchart4:=CALCULATE([NetSales],
DATESBETWEEN(‘Date Values’October 9, 2014,
[first date ttm],
[Last day of previous month]))

What about the chart? Well, next to each other, the two charts so that you can judge for yourself which one looks better.

image
Before (showing partial current month)
image
After (only showing full months)

No more downward slopes, just a full months listed side by side.

Next time, I will reveal my secret recipe for a juicy pot roast. For now, hope this has helped some…

This Post Has 11 Comments
  1. Great post, I’ve run into this very thing so many times and wanted to throw my mouse across the office. Rob’s blog has and is a lifesaver.

  2. Great stuff, I could have used it last weekend. I needed to look back over TTM for date of last visit.

    Operations wants a new app that will give tech’s access to customer files on demand. What is the opportunity for cost savings? Let’s say 15 min saved every time we spends a full day on site when the last visit was more than two weeks ago. I need to calculate the date of the visit preceding the current date.

    Calculated column:
    Last visit: =
    CALCULATE (LASTDATE (Time[Ticketdate]),
    FILTER (Time,Time[CUSJOB]=EARLIER(Time[CUSJOB])),
    DATESINPERIOD (Time[Ticket Date],Time[Ticket Date]-1,-360,day))

    For each customer job look back 360 days from yesterday to find the most recent visit.

    Hat tip to Rob’s rat post, it let me watch the second half of the noon game!

    Number of days layoff no weekend: =
    IF ([Last visit]>0,
    CALCULATE (COUNTROWS (DATE)-2,
    DATESBETWEEN (‘DATE'[Date],Time[Last visit],Time[Ticket Date]),’DATE'[DayNum]<6),0)

    Now I can calculate duration of last visit and slice by job type and tech.

    1. I always appreciate the moment when you “beat” the system by create a go-around formula… Thanks for all your replies.

  3. I’m having a difficult time getting this to work with a formula that calculates inventory turns. I have a fact table with all of the transactions and a date table from 2011 through 2015.

    My turns measure calculates perfectly on a pivot table with Months and Years on the row labels and different inventory categories for column labels, but when I try to create a Power Pivot chart and use the date table as the x-axis, I get all of the dates (not just the TTM) and the line graph is flat no matter how I slice the dates.

    If I use the dates in the fact table, the line graph has some variation from month to month, but it doesn’t calculate correctly.

    Any help would be appreciated.

    1. Aha! I figured it out. My inventory turns measure uses the [Date] column in the formula to calculate average inventory for each month/year, and using that same [Date] column in the TTM measure reset the filter context that the turns measure was using.

      The solution I found was to add another date column in the ‘Date Values’ table:

      Date2 = ‘Date Values'[Date]

      And then use that column in the TTM measure:

      TTMNetUSDchart4:=CALCULATE([Turns],
      DATESBETWEEN(‘Date Values'[Date2],
      [first date ttm],
      [Last day of previous month]))

  4. GRRRRRR!!! … As Indiana reaches for his trusty six shooter, he realizes – damn! I forgot to reload on the 31st of December 2012. Hopefully that makes about as much sense as my pivot dilemma; I have a sheet, disconnected calendar table and a CALCULATE which uses DATESBETWEEN to filter the measure. All works great – except if I have a date of 31st of December 2012 in my calendar. Intrigued yet? .. of course you are – admit it.

    “Have you tried ..” Yes I have. I’ve even managed to replicate the issue in an entirely new sheet as well. Picture this – fruit sales by date in a data table. Calendar has single column – contiguous date range (yes, formatted as date/time). Everyone needs to know how much was spent on buying Pears, so my measure is:
    TotalPears:=CALCULATE(sum(Purchases[PUR_QTY]),Purchases[ITEM]=”Pears”,DATESBETWEEN(Purchases[PURCHASED],FIRSTDATE(Dates[DateNew]),LASTDATE(Dates[DateNew])))

    My calendar table starts with a date of 31/12/2012 and ends on the 30/12/2015 (dd/mm/yyyy). If the 31st DEC 2012 is removed the measure works. If it is in, I get an error “An invalid numeric representation of a date value was encountered.”

    I’d love to send you this bag of fruit (err… I mean the sample worksheet) to demonstrate and see whether you’ve come across any of this before. Failing that, I’m probably going to have to walk away from becoming an Excel pro to take up my life as an adventure seeking archeologist.

  5. .. and in the words of ye great one..
    ..you fell victim to the old “using date intelligence functions on a data table” pitfall!

    ARRGH.. of course! How could I have been so blind – the agony of self realization .. hey… hang on a darn tootin’ minute. Where did the Microsoft documentation ever state that I wonder (grumble).

    I’ll save you the trouble and time people… don’t go looking for the fool’s gold. No, …perhaps Moses did not come down from the mountain with his slate computer that read “do not use date intelligence functions on a data table”, but smarter people than me have travelled far and wide through the PowerPivot kingdom to learn these wisdoms.

    If you indeed feel the need to calculate on a date range,please keep in mind that you need to relate your data table date to your date table (i.e. calendar). Of course this example is simplified, but continuing on with the troubled fruit salad… this will fix things.

    Step 1 – The above pivot model had purchases (with dates) as one data table, the original calendar (called “SlicerDates”) as disconnected table;

    Step 2 – Create another calendar (shall we call it “new_Calendar” ?..
    …I’m OK with that. Let’s move on. ).

    Step 3 – Link “New_Calendar” to the purchase table on dates.

    Step 4 – Rewrite the measure, so that the DATESBETWEEN() function only references the new Calendar as it’s first parameter; for example:
    TotalPears:=CALCULATE(sum(Purchases[PUR_QTY]),
    Purchases[ITEM]=”Pears”,
    DATESBETWEEN(New_Calendar[Dates],
    FIRSTDATE(SlicerDates[Date]),
    LASTDATE(SlicerDates[Date])))

    Lo-and-behold: it works. Life returns to normal. We can all enjoy the 31st of December 2012 again without needing to get it struck out of the history books.

    Oh look.. I broke the website with that ghastly long measure (see how it’s sticking out on the right; geesh) – sorry Rob.

  6. Heck yeah! You, me, and I’m guessing a whole lot of other people are finding out that the logic behind the logic isn’t as crystal clear as we’d hope it would be. However, and that’s what I just love about PowerPivot and DAX, you can layer your logic to suit your needs.

    I’ve been up to my ears on projects these last few weeks, some including PowerPivot work, others just brain work, and I’ve been promising myself to get that Pot Roast Recipe out which will show some more Time Intelligence snazynesssssssss…

    Thanks for the post DBO, I liked the way you fixed your problem, though I am sure that there are easier ways to fix the problem.

  7. Hello Rob,

    I have found your DAX Formulas book to be a great reference but haven’t found a solid example of how to create a formula to effectively identify the first value from a previous date that is not empty, aka blank. For example, I’m attempting to create a KPI that compares the current date/value combination to a prior date/value, aka goal, as long as the prior date is not empty.
    This seems like a common business scenario, but I can’t seem to find a good example using a function such as firstnonblank, etc.
    Your advice would be much appreciated.

    Thanks,

    Ben

    1. How about LASTNONBLANK(Calendar[Date], [Your Measure])?

      Or something like

      LASTNONBLANK(FILTER(ALL(Calendar[Date]), Calendar[Date]< =MAX(Calendar[Date])), [Your Measure])

Leave a Comment or Question