Forum Discussion

PowerRocky12's avatar
PowerRocky12
Helper I
4 years ago
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...
Need Help
  • PowerRocky12's avatar
    PowerRocky12
    4 years ago

    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
v-zhangti's avatar
v-zhangti
Community Support
4 years ago

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])+1
PFY = 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.

PowerRocky12's avatar
PowerRocky12
Helper I
4 years ago

Hey, thanks but I want YTD to work for all fiscal years in the chart. So if todays date is july 20th it would show April 1 - July 20 for all fiscal years. I have been trying to tweak the below forumla but when applied it gives YTD for most recent fiscal year from April 1 to July 20th (Last date in data) but for all other fiscal years it gives me January 1st to July 20th.

YTD=
var _max1 = today() //or maxx(allselected('Order'),'order'[Date])
var _max = format(_max,"MMDD")
return
calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),filter('Date', format('Date'[Date],"MMDD")<=_max))