Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Greg_Deckler

To **bleep** With Time Intelligence

4Horsemen10.jpg

It's a little known fact that there are actually five, not four, Horsemen of the Apocalypse, their names being:

  • Death
  • Famine
  • War
  • Plague
  • Insanity (AKA - DAX Time Intelligence Functions)

So, similar to how I recreated all of those Excel functions, my next fun project has been to unwind the insanity that is the DAX Time "Intelligence" Functions. Sure, I started that a long time ago with my Time Intelligence the Hard Way Quick Measure but I figured the time had finally come to be very specific about creating DAX equivalents for ALL of DAX's Time "Intelligence" functions. 

I do have to say that I really, truly despise DAX's time "intelligence" functions. It's not that I hate a lot of things. Heck, I have two ex-wives and I can't say that I truly despise either one of them really. Don't get me wrong, they don't exactly rank among my favorite people in this world list, but I don't hate them. And even though open source is destroying the world, I don't hate it. Is open source stupid? Obviously, but hate is a strong word, even for open source. It's the same for mathematicians. Sure, mathematicians are evil, but I do not hate them. However, DAX's time "intelligence" functions are another matter. Let me count the ways I despise them:

  1. They are super unintuitive
  2. Their documentation is awful
  3. It's easy to use them incorrectly, fail and get bizarre results
  4. Most are glorified syntax sugar for MAX, MIN, FILTER and CALCULATE
  5. They tend not to work with DirectQuery and RLS
  6. Nearly always require a specific data model layout with a separate date table
  7. Assume a standard calendar and thus are generally useless with custom financial calendars
  8. Some are so mind-numbingly complex or nuanced it is almost certain that you are using them incorrectly and don't know it or haven't accounted for all of the boundary cases. If you haven't thoroughly tested DATEADD, PARALLELPERIOD and DATESINPERIOD it is a good bet you don't fully understand what these are doing all of the time
  9. They seem like they were specifically engineered for use with CALCULATE so if you don't tend to use CALCULATE...

I'm sure there are more, leave your own reasons in the comments. Surely, I cannot be the only person in this world that hates these functions. There must be universal hate for these, right? Certainly everyone on the planet that has written DAX at some point and time has been wronged by time "intelligence" at one point or another. I have to believe that is the case. So, anyway, without further ado, here is your cheat sheet:

DAX Time "Intelligence" Function Equivalent
CLOSINGBALANCEMONTH To **bleep** With OPENINGBALANCE
CLOSINGBALANCEQUARTER To **bleep** With OPENINGBALANCE
CLOSINGBALANCEYEAR To **bleep** With OPENINGBALANCE
DATEADD To **bleep** With DATEADD
DATESBETWEEN To **bleep** With DATESBETWEEN
DATESINPERIOD To **bleep** With DATEADD
DATESMTD To **bleep** With TOTALMTD
DATESQTD To **bleep** With TOTALMTD
DATESYTD To **bleep** With TOTALMTD
ENDOFMONTH To **bleep** With STARTOFMONTH
ENDOFQUARTER To **bleep** With STARTOFQUARTER
ENDOFYEAR To **bleep** With STARTOFYEAR
FIRSTDATE To **bleep** With FIRSTDATE
FIRSTNONBLANK To **bleep** With FIRSTNONBLANK
FIRSTNONBLANKVALUE To **bleep** With FIRSTNONBLANK
LASTDATE To **bleep** With FIRSTDATE
LASTNONBLANK To **bleep** With FIRSTNONBLANK
LASTNONBLANKVALUE To **bleep** With FIRSTNONBLANK
NEXTDAY To **bleep** With NEXTDAY (Lone Biker of the Apocalypse)
NEXTMONTH To **bleep** With NEXTDAY (Lone Biker of the Apocalypse)
NEXTQUARTER To **bleep** With NEXTDAY (Lone Biker of the Apocalypse)
NEXTYEAR To **bleep** With NEXTDAY (Lone Biker of the Apocalypse)
OPENINGBALANCEMONTH To **bleep** With OPENINGBALANCE
OPENINGBALANCEQUARTER To **bleep** With OPENINGBALANCE
OPENINGBALANCEYEAR To **bleep** With OPENINGBALANCE
PARALLELPERIOD To **bleep** With DATEADD
PREVIOUSDAY To **bleep** With NEXTDAY (Lone Biker of the Apocalypse)
PREVIOUSMONTH To **bleep** With NEXTDAY (Lone Biker of the Apocalypse)
PREVIOUSQUARTER To **bleep** With NEXTDAY (Lone Biker of the Apocalypse)
PREVIOUSYEAR To **bleep** With NEXTDAY (Lone Biker of the Apocalypse)
SAMEPERIODLASTYEAR To **bleep** With DATEADD
STARTOFMONTH To **bleep** With STARTOFMONTH
STARTOFQUARTER To **bleep** With STARTOFQUARTER
STARTOFYEAR To **bleep** With STARTOFYEAR
TOTALMTD To **bleep** With TOTALMTD
TOTALQTD To **bleep** With TOTALMTD
TOTALYTD To **bleep** With TOTALMTD

 

FYI, "without further ado" is correct, I checked...

 

Comments