Lagged Values using M

[saanchi2804]

Hi, I have a table similar to below. 

Region	Month of Period End	Attribute	Value
State A	4/1/2022	Homes Sold	24%
State A	3/1/2022	Homes Sold	22%
State A	2/1/2022	Homes Sold	5%
State A	1/1/2022	Homes Sold	6%
State B	5/1/2022	Home Price	8%
State B	4/1/2022	Home Price	9%
State B	3/1/2022	Home Price	2%
State B	2/1/2022	Home Price	34%
State B	1/1/2022	Home Price	22%

I want to create a dynamic column of lagged values such that the period of lag (1 month/2 months/3 months/...) can be selected via a slicer. The calculation of the lag column depicted in the last 3 columns below:

Region	Month of Period End	Attribute	Value	Lagged by 1 (Minus 1 month)	Lagged by 2 (Minus 2 months)	Lagged by 3 (Minus 3 months)
State A	4/1/2022	Homes Sold	24%	22%	5%	..
State A	3/1/2022	Homes Sold	22%	5%	6%	..
State A	2/1/2022	Homes Sold	5%	6%	-	..
State A	1/1/2022	Homes Sold	6%	-	-	..
State B	5/1/2022	Home Price	8%	9%	2%	..
State B	4/1/2022	Home Price	9%	2%	34%	..
State B	3/1/2022	Home Price	2%	34%	22%	..
State B	2/1/2022	Home Price	34%	22%	-	..
State B	1/1/2022	Home Price	22%	-	-	..

Please help me out with what will work. I want to input these columns using M in the power query. Thanks.

[jbwtp · Accepted solution]

Hi @saanchi2804,

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

[saanchi2804]

This was awesome, thanks!