To **bleep** With STARTOFQUARTER

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

1. Ensure that your concept of quarters only covers a standard calendar because if you think STARTOFQUARTER will work with any kind of custom financial calendar then you are sadly, sadly mistaken
2. Create a date table with all possible dates for which you might want to find the start of a quarter
3. Use said date table as the parameter for the STARTOFQUARTER function
4. Ensure that a relationship exists between your fact table and your date table
5. Ensure that the cardinality of the relationship is such that it is Single from your fact table to your date table
6. Stand on your head and spin around 3 times while clicking your heels together and singing "Pump the Jam" by Technotronic

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