Hi,
I'm trying to create a Quarter to Date measure, but we have custom fiscal quarters, with our fiscal year starting in April (so, Q1 would be April-June). When I use the DatesYTD Function to create YTD measures, I can choose my Year End Date, which works well.
However, with the DatesQTD Function, I can't choose a quarter start/end date. I tried creating a measure to compensate, by using the DatesQTD function, and then a DateAdd with adding 3 months:
Won Bookings QTD = CALCULATE([Won Bookings],DATESQTD('Date Table'[Date]),DATEADD('Date Table'[Date],3,MONTH))
But, I get the error below when trying to display this in any type of graphic.
Anyone have any ideas or workarounds? Or am I doing something wrong? Any help would be much appreciated!
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
Helpful resources
Microsoft Fabric Learn Together
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Power BI Monthly Update - April 2024
Check out the April 2024 Power BI update to learn about new features.
Fabric Community Update - April 2024
Find out what's new and trending in the Fabric Community.
