To **bleep** With STARTOFYEAR 07-22-2020 12:18 PM

[Greg_Deckler]

Seriously though, all joking aside...what in the world???

Anyway, similar to how I recreated all of those Excel functions, 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 STARTOFYEAR and ENDOFYEAR which are mind bogglingly stupid DAX functions. 

ToHellWithSTARTOFMONTH = 
  VAR __Date = MAX('Data'[Date1])
RETURN
  DATE(YEAR(__Date),1,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 year 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 STARTOFYEAR, and most other time intelligence functions, to work you must adhere to the following strict guidelines:

1. Create a date table with all possible dates for which you might want to find the start of a quarter
2. Use said date table as the parameter for the STARTOFMONTH function
3. Ensure that a relationship exists between your fact table and your date table
4. Ensure that the cardinality of the relationship is such that it is Single from your fact table to your date table
5. Stand on your head and spin around 3 times while clicking your heels together and singing "Barbie Girl" by Aqua

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 STARTOFYEAR and skip the whole date table thing to begin with? I mean, honestly, where is the value add here folks? 

Also includes ENDOFYEAR replacement:

ToHellWithENDOFYEAR = 
    VAR __Date = MAX('Data'[Date1])
RETURN
    DATE(YEAR(__Date),12,31)

eyJrIjoiOWRjNDhhYTAtZDliNC00YzFlLWI0MTEtMmJmOTZmYzZkYjQ5IiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9

[CliffordAP]

You seem about as angry as I am. I can't believe STARTOFYEAR() requires the actual start of the year to be in the column. This is the problem I had trying to calculate the number of days within the year of a start date;

As you can see, my data only has Jan 2nd instead of jan1st. This threw off the days in year calculation by 1 day. This has got to be fixed. To *BLEEP* with STARTOFYEAR()! This is rediculous!