QTD Measure Calculation For Fiscal Year

[Imagauthamam]

Hi All,

In my project, I calculated the Total Sales MTD,QTD and YTD with respect to calendar year. It was working fine. But there was a request to switch it to Fiscal Year. Our Fiscal Year Starts on April and Ends on March.

Total Cases MTD was working fine anyway.

For, Total Cases YTD, I modified the formula to include when the Fiscal Year ends and this is also working fine. Please find the below formula:

Total Sales YTD = TOTALYTD([Total Sales],Calendar[Calendar Date],"03/31")

For Total Cases QTD, Im not sure how to make this work in measures.

Apr-June ->Q1

July-Sep->Q2

Oct-Dec->Q3

Jan-Mar->Q4

I have used the below formula, how can I make it work with respect to Fiscal Calendar

Total Cases QTD = TOTALQTD([Total Sales],Calendar[Calendar Date])

[Ahmedx · Accepted solution]

plse try this

Measure = 
TOTALYTD(
TOTALQTD([Total Sales],'Calendar'[Date]),'Calendar'[Date],7761)

[Imagauthamam]

@Ahmedx  Thanks for this solution. This works. I'm Just curious to know how did you arrive at this number 7761. And let's say my fiscal year starts from February. So my 

Q1-> Feb to Apr

Q2-> May to July

Q3->Aug to Oct

Q4->Nov to Jan

How Will I tweak the above formula to achieve this?

[Ahmedx]

you can put "03/31" instead
you are reading this

https://www.sqlbi.com/blog/marco/2018/08/10/the-hidden-secrets-of-totalytd/

[ToddChitt]

If Total Sales YTD = TOTALYTD([Total Sales],Calendar[Calendar Date],"03/31") is right, then what is missing from:

Total Cases QTD = TOTALQTD([Total Sales],Calendar[Calendar Date]) ?

Total Cases QTD = TOTALQTD([Total Sales],Calendar[Calendar Date],"03/31")

Does this not tell the function that the first quarter starts on that date? Am I missing something?

[Imagauthamam]

@ToddChitt  It throws an error. Please find the below screenshot.