Lagged Values using M

Hi Anonymous,

I think doing this in PQ would be a bit of overkill, why would you need this chunks of duplicated data to be stored?

Then, how do you want to make it slicer dependant? Via slicer parameter?

I love PQ/M and, probably, a bit sceptical re DAX, but in this case, it looks like a DAX job.

Assuming your table is called Data and you have another table called Offset, which is just one column of 1,2,3,4,5:

which you use as the slicer:

This is the DAX for calculating a measure (which you can add as a column to your visual):

Lagged = 
var dt = FIRSTDATE(DATEADD('Data'[Month of Period End], -minx('Offset', 'Offset'[Offset]), MONTH))
return CALCULATE(SUM('Data'[Value]), Filter(ALL('Data'[Month of Period End]), 'Data'[Month of Period End] = dt))

Cheers,

John