Forum Discussion
Helper VYTD Calculation No Longer Working
Hello,
Would anyone know if there were any updates that have changed how the filters are applied across measures?
I have two measures to calculate the YTD spend - one to calculate the current year and one for previous year.
Until recently, I used the following two measures:
1. Current Year Spend =
VAR LatestMonth = Month(Max('Table'[Date]))
VAR LatestYear = Year(Max('Table'[Date]))
RETURN
CALCULATE(SUM([Spend]), FILTER('Table', [Year] = LatestYear))
2. Previous Year Spend =
VAR LatestMonth = Month(Max('Table'[Date]))
VAR LatestYear = Year(Max('Table'[Date]))
RETURN
CALCULATE(SUM([Spend]), FILTER('Table', [Year] = LatestYear-1 && [Month] <= LatestMonth))
The Previous Year Spend now includes all the months in the year and ignores the last part of the filter which is [Month] <= LatestMonth. If I hardcode the month number (so if I say CALCULATE(SUM([Spend]), FILTER('Table', [Year] = LatestYear-1 && [Month] <= 1)), I get the right answer. 3
What has changed? What am I doing wrong? Please help!
yes this requires a date table.
it is best practice to always create a date table and link it the the fact table. this way, Dax is easier and is more intuitive to understand how Dax will work.
however,
latestMOnth variable will get you 12 because it is evaluated in the context of all the years.
to be able to get the latestmonth of the latest year, you can first create the year variable , which is like you wrote it :
VAR LatestYear = Year(Max('Table'[Date]))
and VAR LatestMonth = calculate( Month(Max('Table'[Date])) , year(table[date]) = Latestyear )
and then you can continue your code as you wrote it.
is this what you need, or am i missing something ?
6 Replies
- Fowmy
Super User
newpbiuser01
How are you visualizing these measures, Have you included the date column in the visual? are you using a calendar table?
Share the screenshot of your model and and the visual.
newpbiuser01Hi Fowmy ,
I don't have a calendar table - I get the data with month and year that I combine to create the "date" column. The table looks similar to the one below. I need to show these two ways:
1. As a KPI on the top in the card visual so who the YTD YoY Change.
2. On a barchart that shows the spend by year (YTD)
- Daniel29195
Community Champion
if this is the intended result :
you can use the following measure ;
Measure 7 =if(ISFILTERED('Date Table'[Year]),CALCULATE([Revenue],sameperiodlastyear(DATESYTD('Date Table'[date]))),VAR get_max_year = MAX('Date Table'[Year])VAR res =CALCULATE([Revenue],value('Date Table'[Year]) = get_max_year - 1, all('Date Table'))returnres)NB : all('Date Table') is optional in case you have set the date table : mark as date table.hope this helps- newpbiuser01
Hi Daniel29195 ,
This solution requires us to have a date table right? Is there a way to do this without the date table? I ask because we have cases where we want to show the YTD previous year, and YTD previous year minus 1.
There must be a way to use a measure to calculate the latest month, and then use that as a "constant" to filter the spend for a specific year?
What I mean by that is, if I have the following variables,
VAR LatestMonth = Month(Max('Table'[Date]))
VAR LatestYear = Year(Max('Table'[Date]))
and I apply these as filters together:
Filter('Table', [Month] <= LatestMonth && [Year] = PreviousYear), this should be evaluated as:
Filter ('Table', [Month] <= 1 && [Year] = 2023)
Instead, it looks like Power BI goes back and re-evaluates LatestFM based on the LatestFY, and does:
Filter('Table', [Month] <= 12 && [Year] = 2023)
Is there a way to use the calculated value from the LatestMonth measure without it being re-evaluated? or by still keeping year to be the latest year?
- Daniel29195
yes this requires a date table.
it is best practice to always create a date table and link it the the fact table. this way, Dax is easier and is more intuitive to understand how Dax will work.
however,
latestMOnth variable will get you 12 because it is evaluated in the context of all the years.
to be able to get the latestmonth of the latest year, you can first create the year variable , which is like you wrote it :
VAR LatestYear = Year(Max('Table'[Date]))
and VAR LatestMonth = calculate( Month(Max('Table'[Date])) , year(table[date]) = Latestyear )
and then you can continue your code as you wrote it.
is this what you need, or am i missing something ?
