Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
07-21-2020 19:49 PM - last edited 07-22-2020 10:53 AM
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 will be to unwind the insanity that is the DAX Time Intelligence Functions. Sure, I started that a long time ago but might as well get specific. We will start this little endeavor with STARTOFQUARTER. So much hardship over a dirt simple SWITCH statement.
ToHellWithSTARTOFQUARTER =
VAR __Date = MAX('Data'[Date1])
VAR __Month = MONTH(__Date)
VAR __Year = YEAR(__Date)
RETURN
SWITCH(TRUE(),
__Month <= 3,DATE(__Year,1,1),
__Month <= 6,DATE(__Year,4,1),
__Month <= 9,DATE(__Year,7,1),
DATE(__Year,10,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 quarter 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 STARTOFQUARTER, 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 SWITCH statement??? Or, better yet, why not just use the logic encapsulated here and codify it as the function STARTOFQUARTER and skip the whole date table thing to begin with? I mean, honestly, where is the value add here folks? Sure, I could understand it if you could point it to a custom financial calendar and it would work to find the custom quarter start dates but it literally only works with a standard calendar. So if it only ever works with a standard calendar then what possible use is it to point it to a table of dates? It makes zero sense. You know the date of the start of the quarter on a standard calendar without such nonsense. Hence the insanity...
Bonus, if you use this method instead, you can support any custom financial calendar and custom quarter start dates that you could ever conceive of, but, you know, that's the least of what's wrong with STARTOFQUARTER...
Also includes ENDOFQUARTER replacement
ToHellWithENDOFQUARTER =
VAR __Date = MAX('Data'[Date1])
VAR __Month = MONTH(__Date)
VAR __Year = YEAR(__Date)
RETURN
SWITCH(TRUE(),
__Month <= 3,DATE(__Year,3,31),
__Month <= 6,DATE(__Year,6,30),
__Month <= 9,DATE(__Year,9,30),
DATE(__Year,12,31)
)
eyJrIjoiNDYzZGFiZTktOTlmZi00NWQ0LTk0YjEtNjc2ZGY3OWQwN2YyIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9