Hi All,
I hope someone can help me out. I've been trying to fgure out how to calculate QTD figures for a Financial Report without success.
I have the below formula which to calculate YTD and it's working fine:
YTD = CALCULATE([Total Amount],(DATESYTD(dcalendar[Date],"08/31"))))
My fiscal year is running from Sep to Aug, hence the 1st Quarter is Sep-Oct-Nov and so on.
I have a calendar table called "dcalendar" with the below calculated columns:
Date: dcalendar = CALENDAR(DATE(YEAR(MIN(fSwedenQ1_Q2[Date])),1,1),DATE(YEAR(MAX(fSwedenQ1_Q2[Date])),12,31))
Year = YEAR(dcalendar[Date])
Month = dcalendar[Date].[Month]
MonthNumber = dcalendar[Date].[MonthNo]
Fiscal Year = if(dcalendar[MonthNumber]<9,dcalendar[Year],dcalendar[Year]+1)
Fiscal Quarter = IF((dcalendar[MonthNumber]>=9 && dcalendar[MonthNumber]<12),"Qtr1",IF((dcalendar[MonthNumber]>=6 && dcalendar[MonthNumber]<9),"Qtr4",IF((dcalendar[MonthNumber]>=3 && dcalendar[MonthNumber]<6),"Qtr3","Qtr2")))
Fiscal Quarter number = IF((dcalendar[MonthNumber]>=9 && dcalendar[MonthNumber]<12),"1",IF((dcalendar[MonthNumber]>=6 && dcalendar[MonthNumber]<9),"4",IF((dcalendar[MonthNumber]>=3 && dcalendar[MonthNumber]<6),"3","2"))).
How can I calculate QTD amounts? Jugding by other posts the QTD function won't be good in this case.
Thanks in advance.
VE
