Forum Discussion

ROCKYDO12's avatar
ROCKYDO12
Helper III
4 years ago
Solved

Multi-year YTD Slicer

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 d...
Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

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

amitchandak's avatar
amitchandak
Super User
4 years ago

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's avatar
    ROCKYDO12
    Helper III
    4 years ago

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

     

  • ROCKYDO12's avatar
    ROCKYDO12
    Helper III
    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