Forum Discussion
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 every year), instead of just the last 12 months.
YTD Actuals = CALCULATE ( SUM ( 'finance ExpensesWithAccountDescriptions '[ Total Amount ] ), DATESINPERIOD ( ' New Calender Table '[ Dates ], today () , -1 , YEAR ))
Also, the "today()" portion is not syncing with the slicer. The data remains the same no matter what date I select on the slicer, even though the other columns all make the adjustments.
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.
2 Replies
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)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.
