Forum Discussion

Anonymous's avatar
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...
Need Help
  • v-yanjiang-msft's avatar
    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.

  • v-yanjiang-msft's avatar
    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.

v-yanjiang-msft's avatar
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.

Anonymous's avatar
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's avatar
    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.