Multi-year YTD Slicer

[ROCKYDO12]

I am trying to create a YTD slicer in my report that will filter all Fiscal Years and show amounts as of last transaction date in current year and all past fiscal years will show data up until that date as well. In my data set if the last transation date is July 17, 2022, then once you apply the YTD slicer it will filter all past fiscal years and show amounts for the same date range, up until July 17th. My fiscal Year starts April 1. I can't seem to find any posts that will give me my desired result

[amitchandak · Accepted solution]

@ROCKYDO12 , Try like

YTD =
var _omax = format(if(isfiltered('Date'),MAXX( allselected( 'Date') , 'Date'[Date]) , today()), "MMDD")
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _min = if(month(_max) <4, Date(year(_max)-1,4,1) ,Date(year(_max),4,1) )
return
CALCULATE(calculate([net], filter('Date'), format('Date'[Date], "MMDDD") <=_omax) ,DATESBETWEEN('Date'[Date],_min,_max))

or

YTD QTY forced=
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))

[ROCKYDO12]

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

[ROCKYDO12]

Hey, the formula for YTD forced is working for Current year but for any previous year it's giving me YTD amounts based on calender year. I tried making a few changes but no luck. 

YTD QTY forced=
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))