To **bleep** With Time Intelligence

[Greg_Deckler]

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...

[Kolumam]

Loved this article!

[lbendlin]

Interesting absence of EDATE()... Does it mean that gets a pass?

[Greg_Deckler]

@lbendlin - I only focused on the DAX functions listed under Time "Intelligence". EDATE is listed under Date and Time functions.

https://docs.microsoft.com/en-us/dax/time-intelligence-functions-dax

https://docs.microsoft.com/en-us/dax/date-and-time-functions-dax

[Daviejoe]

Haha, great article, love the picture!

[Greg_Deckler]

@Daviejoe - Glad someone gets my humor! 🙂

[Anonymous]

Thanks for sharing @Greg_Deckler . Loved it....Bleeping 🙂