cancel
Showing results for
Did you mean:
Highlighted
Regular Visitor

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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV

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

@mhussey , 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

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

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://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-Y...

My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Super User IV

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

@mhussey , 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

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

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://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-Y...

My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Announcements

#### August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

#### Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

#### Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

#### Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors