cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors