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 attached a test file below. 

 

https://drive.google.com/file/d/1N6sgJR2pUm5HvWNhFlQVnfGdJPfF44EN/view?usp=sharing

 

 

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

5 Replies

  • 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

      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
    • 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))