To **bleep** With STARTOFMONTH
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:
- Death
- Famine
- War
- Plague
- Insanity (AKA - DAX Time Intelligence Functions)
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:
- Create a date table with all possible dates for which you might want to find the start of a month
- Use said date table as the parameter for the STARTOFMONTH function
- Ensure that a relationship exists between your fact table and your date table
- Ensure that the cardinality of the relationship is such that it is Single from your fact table to your date table
- Stand on your head and spin around 3 times while clicking your heels together and singing "My Humps" by Black Eyed Peas
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)