Need FISCAL YTD formula + Slicer

[dtango9]

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.

[v-yangliu-msft · Accepted solution]

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]

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