Hi, I have a table similar to below. RegionMonth of Period EndAttributeValueState 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. Will prefer the DAX to be as simple as possible since I do not have a coding background. Thanks.

Anonymous

Not applicable

3 years ago

Solved

Lagged Values

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. Will prefer the DAX to be as simple as possible since I do not have a coding background. Thanks.

Need Help

v-yanjiang-msft

3 years ago

Hi Anonymous ,

According to your description, here's my solution.

Create three calculated columns.

Lagged by 1 =
MAXX (
    FILTER (
        'Table',
        'Table'[Region] = EARLIER ( 'Table'[Region] )
            && EOMONTH ( 'Table'[Month of Period End], 0 )
                = EOMONTH ( EARLIER ( 'Table'[Month of Period End] ), -1 )
    ),
    'Table'[Value]
)

Lagged by 2 =
MAXX (
    FILTER (
        'Table',
        'Table'[Region] = EARLIER ( 'Table'[Region] )
            && EOMONTH ( 'Table'[Month of Period End], 0 )
                = EOMONTH ( EARLIER ( 'Table'[Month of Period End] ), -2 )
    ),
    'Table'[Value]
)

Lagged by 3 =
MAXX (
    FILTER (
        'Table',
        'Table'[Region] = EARLIER ( 'Table'[Region] )
            && EOMONTH ( 'Table'[Month of Period End], 0 )
                = EOMONTH ( EARLIER ( 'Table'[Month of Period End] ), -3 )
    ),
    'Table'[Value]
)

Get the result:

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Lagged Values.pbix20 KB

v-yanjiang-msft

3 years ago

Hi Anonymous ,

My pleasure!

You can do it in Power Query. Add three custom columns.

try Table.SelectRows(#"Changed Type",(x)=>x[Region]=[Region]and x[Month of Period End]<[Month of Period End])[Value]{0} otherwise ""

try Table.SelectRows(#"Changed Type",(x)=>x[Region]=[Region]and x[Month of Period End]<[Month of Period End])[Value]{1} otherwise ""

try Table.SelectRows(#"Changed Type",(x)=>x[Region]=[Region]and x[Month of Period End]<[Month of Period End])[Value]{2} otherwise ""

Result:

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Lagged Values.pbix23 KB

3 Replies

v-yanjiang-msft

Community Support

3 years ago

Hi Anonymous ,

According to your description, here's my solution.

Create three calculated columns.

Lagged by 1 =
MAXX (
    FILTER (
        'Table',
        'Table'[Region] = EARLIER ( 'Table'[Region] )
            && EOMONTH ( 'Table'[Month of Period End], 0 )
                = EOMONTH ( EARLIER ( 'Table'[Month of Period End] ), -1 )
    ),
    'Table'[Value]
)

Lagged by 2 =
MAXX (
    FILTER (
        'Table',
        'Table'[Region] = EARLIER ( 'Table'[Region] )
            && EOMONTH ( 'Table'[Month of Period End], 0 )
                = EOMONTH ( EARLIER ( 'Table'[Month of Period End] ), -2 )
    ),
    'Table'[Value]
)

Lagged by 3 =
MAXX (
    FILTER (
        'Table',
        'Table'[Region] = EARLIER ( 'Table'[Region] )
            && EOMONTH ( 'Table'[Month of Period End], 0 )
                = EOMONTH ( EARLIER ( 'Table'[Month of Period End] ), -3 )
    ),
    'Table'[Value]
)

Get the result:

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Lagged Values.pbix20 KB

Anonymous
Not applicable
3 years ago
Hi, thank you for the answer. This works if I did not have to use a slicer to select the number of months I wanted to lag my values by. However to use a slicer, I need to pivot these three rows which can only be done in PowerQuery. Could I replicate the same calculated columns in PowerQuery using M?
- v-yanjiang-msft
  Community Support
  3 years ago
  Hi Anonymous ,
  
  My pleasure!
  
  You can do it in Power Query. Add three custom columns.
  
  try Table.SelectRows(#"Changed Type",(x)=>x[Region]=[Region]and x[Month of Period End]<[Month of Period End])[Value]{0} otherwise ""
  try Table.SelectRows(#"Changed Type",(x)=>x[Region]=[Region]and x[Month of Period End]<[Month of Period End])[Value]{1} otherwise ""
  try Table.SelectRows(#"Changed Type",(x)=>x[Region]=[Region]and x[Month of Period End]<[Month of Period End])[Value]{2} otherwise ""
  
  Result:
  
  I attach my sample below for your reference.
  
  Best Regards,
  Community Support Team _ kalyj
  
  If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
  Lagged Values.pbix23 KB

Forum Discussion

Lagged Values

3 Replies

Related content

Lagged Values using M

Scale embedded sku level automatically - without lag?

Lag function in DAX

SQL endpoint lags behind lakehouse

Compute Lead and Lag