Forum Discussion
Solved
YTD Multi Year Comparison
Hey, I am hoping someone can help me. This slicer would filter my tables to show YTD for all fiscal years within that table or chart. I haven't found anything that has really worked. I have attac...
I used this formula and got my expected results:
YTD Amount =VAR _SOY="04/01/" & YEAR(TODAY())VAR _DOY=DATEDIFF(_SOY,TODAY(),DAY)VAR SPPY=CALCULATE(sum('Table'[Amount]),FILTER('Calendar','Calendar'[Day of Fiscal Year Number]<=_DOY))RETURNSPPY
Hi, PowerRocky12
You can try the following methods, I hope it will help you. Create a new date table and calculate the financial year.
Table:
Date = CALENDAR(MIN('Table'[Date]),MAX('Table'[Date]))Column:
Start date of the financial year = IF(MONTH([Date])<4,DATE(Year([Date])-1,4,1),DATE(Year([Date]),4,1))FY = YEAR([Start date of the financial year])&"-"&YEAR([Start date of the financial year])+1PFY = YEAR([Start date of the financial year])-1&"-"&YEAR([Start date of the financial year])
Measure:
Dynamic =
SWITCH(True(),
SELECTEDVALUE('Slicer'[Full Year & YTD])="Full Year",
CALCULATE(SUM('Table'[Amount]),
FILTER(ALL('Date'),[FY]=SELECTEDVALUE('Date'[FY]))),
SELECTEDVALUE(Slicer[Full Year & YTD])="YTD",
CALCULATE(SUM('Table'[Amount]),
YEAR('Date'[Start date of the financial year])=YEAR(TODAY())))
Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I used this formula and got my expected results:
YTD Amount =
VAR _SOY="04/01/" & YEAR(TODAY())
VAR _DOY=DATEDIFF(_SOY,TODAY(),DAY)
VAR SPPY=
CALCULATE(sum('Table'[Amount]),FILTER('Calendar','Calendar'[Day of Fiscal Year Number]<=_DOY))
RETURN
SPPY