DAX - ENDOFQUARTER results

[TobiasC]

I'm wanting to use the ENDOFQUARTER DAX function but am finding I'm getting an odd result.

in my view I should get the following list:

March Qtr = 31 Mar YY

June Qtr = 30 Jun YY

Sept Qtr = 30 Sep YY

Dec Qtr = 31 Dec YY

However the formula below seems to be returning the 1st day of the next quarter; ie 1 April, 1 July, 1 Sept, 1 December.

=ENDOFQUARTER(DateTime[DateKey])  

Just wondering if I'm doing something wrong? 

Also something semi related, do you need to have a separate date table to make time intelligence functions work?

[v-yuezhe-msft · Accepted solution]

Hi @TobiasC,

I am not able to reproduce your issue, when I use ENDOFQUARTER function in a Date table, it returns the correct value.  Could you please share sample data of your scenario?

This similar blog also shows how to use ENDOFQUARTER function.

Thanks,
Lydia Zhang

[TobiasC]

Hi @v-yuezhe-msft,

Thank you for the link.  Looks like it was probably my data! I've tried it on a similar data set (I discarded what I had been working on) and seems to come out OK.

Am I right that if the last day of the quarter isn't in the data set say 30 Sept that the function will report the higest value that is in the quarter?

Cheers, Tobias

[v-yuezhe-msft]

Hi @TobiasC,

Based on my test, even if the last day of the quarter(for example, 9/30/2016) is in the Date table, the ENDOFQUARTER function will return the correct value(9/30/2016) for it.

Thanks,
Lydia Zhang