Start calculation from last year of max date (Running Average)

[Astro1990]

Hi everyone.

I am struggling abit with this running average.

Measure =
CALCULATE(
DIVIDE((SUM('Sheet1'[Aqua])*358),(sum('sheet1'[Wlk3]))),
FILTER(
ALLSELECTED('Sheet1'[Date]),
ISONORAFTER('Sheet1'[Date], MAX('Sheet1'[Date]), DESC)
)
)

It works really good, but I only want the calculation starting last year from MAX.

Since I have data from 3-4 years ago, which would not be relevant for me.

Is there a way to start the calculation from exactly 1 year ago?

Thank you so much in advance! 😀

Let me know if there is any other data I should show.

[Astro1990]

Thank you so much Rico!

It worked wonderfully!

[Astro1990]

I think I corrected it. Instead of Filter, I used DatesinPeriod.
It looks great! Still checking if it, so I'll post here once I finished checking. 

FILTER(
ALLSELECTED('Sheet1'[Date]),
ISONORAFTER('Sheet1'[Date], MAX('Sheet1'[Date]), DESC)
)
)

To the following:

    DATESINPERIOD(

    DATESINPERIOD(
'Sheet1'[Date],
LASTDATE('Sheet1'[Date]),
-12,
MONTH
    )
)

[v-rzhou-msft · Accepted solution]

Hi @Astro1990 ,

As far as I know, DATESINPERIOD() will work if you have continuous date in your data model. So if there is continuous date in Sheet1, you will get correct result. You can add a dimdate table by CALENDAR() or CALENDARAUTO() with continuous date as well.

I think you can achieve your goal with Filter() function as well. Try this code.

Measure = 
VAR _LASTDATE = MAXX(ALL(Sheet1),Sheet1[Date])
RETURN
CALCULATE(
DIVIDE((SUM('Sheet1'[Aqua])*358),(sum('sheet1'[Wlk3]))),
FILTER(
Sheet1,
Sheet1[Date] > EOMONTH(_LASTDATE,-12)
)
)

Best Regards,
Rico Zhou

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