07-22-2020 11:53 AM - last edited 07-23-2020 08:06 AM
Continuing in my quest to create simple replacements for DAX's Time "Intelligence" functions, it's a little known fact that there are actually five, not four, Horsemen of the Apocalypse, their names being:
So, similar to how I recreated all of those Excel functions, I think my next fun project is unwiding the insanity that is the DAX Time Intelligence Functions. Sure, I started that a long time ago but might as well get specific. This all started with To **bleep** With STARTOFQUARTER. Here we cover STARTOFMONTH and ENDOFMONTH which are, amazingly, even more inane than STARTOFQUARTER and ENDOFQUARTEr. So much hardship over simple DATE formulas.
ToHellWithSTARTOFMONTH = VAR __Date = MAX('Data'[Date1]) VAR __Month = MONTH(__Date) VAR __Year = YEAR(__Date) RETURN DATE(__Year,__Month,1)
Fail 1 is when you might, quite naturally, think that you should feed the date you are trying to find the start of the month for in as a parameter. Fail.
Fail 2 is when you pass in the parameter of a date column in a date table but you don't have a relationship with that particular date table that is in context. Fail.
When it actually works, you have to have a relationship with your date table in context AND pass in the parameter of a date column in that date table. Oh, except that nice blank row that you get... Fail.
Turns out, in order to get STARTOFMONTH, and most other time intelligence functions, to work you must adhere to the following strict guidelines:
And somehow this is better than just using a DATE statement??? Or, better yet, why not just use the logic encapsulated here and codify it as the function STARTOFMONTH and skip the whole date table thing to begin with? I mean, honestly, where is the value add here folks?
Also includes ENDOFMONTH replacement
ToHellWithENDOFMONTH = VAR __Date = MAX('Data'[Date1]) RETURN EOMONTH(__Date,0)