Forum Discussion
Lagged Values using M
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.
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
2 Replies
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