Hi - I'm trying to build an MAT measure as a calculated field in a pivot table. (DAX).

I have weekly data.

I used the LAST Date to get the most recent date of sales but when I go to the date 1 year back, I think it fetches the last date of that month.

I've worked out that my MAT measure runs from 01/04/2019 - 09/03/2020 (missing out 18/03/19 and 25/03/19).

The expression I used was:

=CALCULATE (
SUM (Weekly_Flat_File[VALUES STG] ),
DATESINPERIOD ( Weekly_Flat_File[Week Commencing], LASTDATE (Weekly_Flat_File[Week Commencing] ), -1, YEAR ))

(week commencing = date)
(VALUES STG = Sales)

So I need to find the most recent date every time the flat file is refreshed and then the corresponding date 1 year back.

09/03/20 52 weeks back = week 12 2019 - 18/03/2019 - week 11 2020 - 09/03/2020

There are columns in the data for Week, Week Number and Year formatted like WK1120, 11, 2020 if it would be easier to somehow use those?

Thanks for any advice

Mark