YTD Multi Year Comparison

[PowerRocky12]

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

[v-zhangti]

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 · Accepted solution]

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]

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

[ribisht17]

@PowerRocky12 

Please check this one Solved: YTD comparison of different years - Microsoft Power BI Community

Regards,

Ritesh

[PowerRocky12]

Hey, thanks but the PBIX file was deleted in the link