DAX Filter for Calculating/Displaying YTD to last month of available data

[Anonymous]

Hi All,

Nubie here, would be very thankful for your help.

I have a graph displaying YTD Budget  Vs YTD Actual.

I have budget data for the full year but Actual data only up to the previous month.

As a result the actual YTD figure is the same for the last few months of the year and I would rather the actual only displayed up to the month I have data for.

I  used this formula to calculate YTD (our Year runs Oct to Sep)

Unit Budget YTD = CALCULATE(TOTALYTD([Unit Budget Total],'Date Dim'[Date],ALL('Date Dim'),"30,9"))

I have used this formula for the Actual

Unit YTD = CALCULATE(TOTALYTD([Unit Total],'Date Dim'[Date],ALL('Date Dim'),"30,9"),FILTER('Date Dim','Date Dim'[Date].[Date] <= TODAY()))

The filter in green helps remove the last couple of months of data, but still leaves an extra month(current month) of repetition (see graph below):

 I am looking for a filter to help me stop the calculation at the last available month of data, which would always be the data for the previous month.

Thanks in advance for your help.

[amitchandak · Accepted solution]

@Anonymous , find these example to control YTD

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

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


YTD QTY forced= 
var _max = maxx('order',[Order date])
return
calculate(Sum('order'[Qty]),DATESYTD('Date'[Date],"9/30"),'Date'[Date]<=_max)

LYTD QTY forced= 
var _max1 =maxx('order',[Order date])
var _max = date(year(_max1)-1,month(_max1),day(_max1))
return
CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year),"9/30"),'Date'[Date]<=_max)
//TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.