Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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.
Solved! Go to 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
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
This was awesome, thanks!
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
62 | |
40 | |
36 | |
28 | |
15 |