cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## 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.

1 ACCEPTED SOLUTION
Memorable Member

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

2 REPLIES 2
Memorable Member

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

Helper I

This was awesome, thanks!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors