Guest Post by Willem van Dijk
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()…
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.
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
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
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.
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
[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.
Before (showing partial current month)
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…