Lagged Values

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.

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.