Return Start of Quarter Date Column is returning the wrong date

[Anonymous]

Hello,

I'm trying to return the start of the quarter date in a calculated column. 

This is the claculation i'm using: STARTOFQUARTER(LOA[FILE DATE]).

The [FILE DATE] is 6/1/2020 and the calculated column returns 6/1/2020, instead of 4/1/2020.

I would normally add a start of quarter using the query editor, but i'm planning on using

the start of quarter for an if statement that goes,

IF(LOA[BEGIN DATE]<LOA[FILE DATE], STARTOFQUARTER(LOA[FILE DATE]), LOA[BEGIN DATE]. 

IS Start of quarter not allowed in calculated columns?

Help is much aprreciated.

Thanks

[Greg_Deckler · Accepted solution]

@Anonymous - So normally for time intelligence functions to work you want a date table and you would reference that date table. If you reference the documentation you can see this: https://docs.microsoft.com/en-us/dax/startofquarter-function-dax but it really isn't obvious in my opinion. The key being that you are feeding a bunch of dates to it as its parameter. Looks like you are only feeding it a single date as a parameter so that means that's the date that it is going to return. To get this to work the time intelligence way, you would need a date table that is related to that date column that you are trying to find the start of the quarter for (is filtered by it).

Anyway, time intelligence functions are really just fancy filters that don't work intuitively. So, IMHO, skip them and just code it directly so you know exactly what is going on. See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008

It's a simple SWITCH statement:

Start of Quarter = 
  VAR __Date = 'Table'[Date] // or MAX('Table'[Date]) if measure
  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)
  )
    

[Anonymous]

Thank you!

[Greg_Deckler]

@Anonymous - You are welcome. You have inspired my next project, the start of which can be found here:

https://community.powerbi.com/t5/Quick-Measures-Gallery/To-bleep-With-STARTOFQUARTER/td-p/1240067