Forum Discussion

dtango9's avatar
dtango9
Helper II
5 years ago
Solved

Need FISCAL YTD formula + Slicer

  Hello, I created a dashboard and it is almost complete!  I'm just looking to edit the calculated measure shown below so that it represents Fiscal Year-to-Date (that begins Dec 1 to Nov 30...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi  dtango9 ,

    According to your description, the data from October 1, 2019 to December 31, 2020 is created:

     

    Here are the steps you can follow：

    1. Create Measure.

    Measure =
CALCULATE(SUM('Table'[amount]),FILTER(ALLSELECTED('Table'),[Date]<=TODAY()&&[Date]>=DATE(YEAR('Table'[Date])-1,MONTH(TODAY()),DAY(TODAY()))))

    2. Result.

    Your measure filter does not generate you. You can add allselect() or all() functions to the function. For this kind of content, you can check the related link:

    https://docs.microsoft.com/en-us/dax/all-function-dax

    https://docs.microsoft.com/en-us/dax/allselected-function-dax

     

    You can downloaded PBIX file from here.

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

amitchandak's avatar
amitchandak
Super User
5 years ago

dtango9 , Dateytd can take end date of year

 

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"11/30"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"11/30"))

 

 

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],"11/30"),filter('Date', format('Date'[Date],"MMDD")<=_max))


YTD QTY forced=
var _max = today()
return
if(max('Date'[Date])<=_max, calculate(Sum('order'[Qty]),DATESYTD('Date'[Date],"11/30")), blank())
//or
//calculate(Sum('order'[Qty]),DATESYTD('Date'[Date],"11/30"),filter('Date','Date'[Date]<=_max))
//calculate(TOTALYTD(Sum('order'[Qty]),'Date'[Date],"11/30"),filter('Date','Date'[Date]<=_max))

 

LYTD QTY forced=
var _max = date(year(today())-1,month(today()),day(today()))
return
if(max('Date'[Date])<=_max, CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year),"11/30"),'Date'[Date]<=_max), blank())
//OR
//CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year),"11/30"),'Date'[Date]<=_max)