It's a little known fact that there are actually five, not four, Horsemen of the Apocalypse, their names being:
- 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:
- They are super unintuitive
- Their documentation is awful
- It's easy to use them incorrectly, fail and get bizarre results
- Most are glorified syntax sugar for MAX, MIN, FILTER and CALCULATE
- They tend not to work with DirectQuery and RLS
- Nearly always require a specific data model layout with a separate date table
- Assume a standard calendar and thus are generally useless with custom financial calendars
- 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
- 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:
FYI, "without further ado" is correct, I checked...