Lagged Values

[saanchi2804]

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.

[v-yanjiang-msft · Accepted solution]

Hi @saanchi2804 ,

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.

[v-yanjiang-msft · Accepted solution]

Hi @saanchi2804 ,

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.

[saanchi2804]

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 · Accepted solution]

Hi @saanchi2804 ,

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.