Skip to main content
cancel
Showing results for 
Search instead 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.

Reply
saanchi2804
Helper I
Helper I

Lagged Values using M

Hi, I have a table similar to below. 

RegionMonth of Period EndAttributeValue
State A4/1/2022Homes Sold24%
State A3/1/2022Homes Sold22%
State A2/1/2022Homes Sold5%
State A1/1/2022Homes Sold6%
State B5/1/2022Home Price8%
State B4/1/2022Home Price9%
State B3/1/2022Home Price2%
State B2/1/2022Home Price34%
State B1/1/2022Home Price22%

 

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:

RegionMonth of Period EndAttributeValueLagged by 1 (Minus 1 month)Lagged by 2 (Minus 2 months)Lagged by 3 (Minus 3 months)
State A4/1/2022Homes Sold24%22%5%..
State A3/1/2022Homes Sold22%5%6%..
State A2/1/2022Homes Sold5%6%-..
State A1/1/2022Homes Sold6%--..
State B5/1/2022Home Price8%9%2%..
State B4/1/2022Home Price9%2%34%..
State B3/1/2022Home Price2%34%22%..
State B2/1/2022Home Price34%22%-..
State B1/1/2022Home Price22%--..

 

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

1 ACCEPTED SOLUTION
jbwtp
Memorable Member
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:

jbwtp_0-1666733445423.png

which you use as the slicer:

jbwtp_1-1666733472298.png

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))

 

 

jbwtp_2-1666733588202.png

 

 

Cheers,

John

 

 

View solution in original post

2 REPLIES 2
jbwtp
Memorable Member
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:

jbwtp_0-1666733445423.png

which you use as the slicer:

jbwtp_1-1666733472298.png

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))

 

 

jbwtp_2-1666733588202.png

 

 

Cheers,

John

 

 

This was awesome, thanks!

Helpful resources

Announcements
Microsoft Fabric Learn Together

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

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors