Forum Discussion
QTD Function with Custom Fiscal Quarters
Hi BKnecht,
You can create a calculated column to return fiscal quarter, the sample DAX would like below.
FYQuarter = "FY "&IF(MONTH(FiscalQTD[Date])>=4,YEAR(FiscalQTD[Date])+1,YEAR(FiscalQTD[Date]))&" Quarter"&CEILING(IF(MONTH(FiscalQTD[Date])>=4,(MONTH(FiscalQTD[Date])-3)/3,(MONTH(FiscalQTD[Date])+9)/3),1)The create the QTD column use the DAX below.
QTD = CALCULATE(SUM(FiscalQTD[Sales]),FILTER(ALLEXCEPT(FiscalQTD,FiscalQTD[FYQuarter]),FiscalQTD[Date]<=EARLIER(FiscalQTD[Date])))The report looks like below.
Regards,
Charlie Liao
There is likely a 'correct way' to do this, using built in functions. But, I typically don't do it that way, maybe cuz I'm silly.
The easy way to do this is have a calculated column in your calendar table that is the QuarterNumber -- that always increases even across years. Then just write the measure using that calc column... something like:
Won Bookings QTD = CALCULATE([Won Bookings], FILTER(ALL('Date Table'), 'Date Table'[Date] <= MAX('Date Table'[Date]) && 'Date Table'[QuarterNumber] = MAX('Date Table'[QuarterNumber])))
(Alternatively, you could have a Quarter number that does reset each year... and include Year in your measure).
Hey Scottsen,
Thanks for the reply. One thing I'm not understanding is the QuarterNumber calculated column - would that just be a column that runs through whole numbers, starting at 1, then 2, then 3, etc?
Yep!
That makes complete sense. My only problem is that I don't know how to create a calculated column that just gives me ordered numbers starting at 1 and then does down haha. Any ideas? Seems simple to me but I can't think of a way to do it with DAX.
Thank you!
