topic Re: Year Over Year Change in Moving Average in DAX Commands and Tips

Year Over Year Change in Moving Average

Dave1mo1 — Mon, 29 Jul 2024 12:30:47 GMT

Hi,

I have a measure below that calculates the % change in a moving average, comparing the current year to the previous year. I have it on a map visual to show it by state, but it only works correctly if I have a visual-level filter for yesterday's date on the visual (our data is only current through yesterday).

How can I rewrite this measure to only show results for THE MOST RECENT date in my date table, which is also the date for my most recent transaction?

Booked - YoY Unit MA =

calculate(
divide (
[Booked - Unit MA], [Booked - PY Unit MA], 0)
-1)

Re: Year Over Year Change in Moving Average

SachinNandanwar — Mon, 29 Jul 2024 19:08:29 GMT

VAR MostRecentDate = MAX('Table'[Date])

and then filter inside the CALCULATE for MostRecentDate

Re: Year Over Year Change in Moving Average

Anonymous — Tue, 30 Jul 2024 08:53:02 GMT

Hi Dave1mo1 ,

Based on the description, try to use the following dax formula.

Booked - YoY Unit MA = CALCULATE( DIVIDE( [Booked - Unit MA], [Booked - PY Unit MA], 0 ) - 1, LASTDATE('DateTable'[Date]) )

LASTDATE function (DAX) - DAX | Microsoft Learn

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.