Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Lookup Record Value from Previous Month

I am trying to understand the most efficient way to look up changes between cell values within an appended query.   Background: Each month a new excel file is appended to create a 'Combined' query ...
Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    3 years ago

    Hi Anonymous ,

    It's my pleasure!

    You can create another calculated column.

    Last month value =
MAXX (
    FILTER (
        'Table',
        'Table'[Record ID] = EARLIER ( 'Table'[Record ID] )
            && MONTH ( 'Table'[Month] )
                = MONTH ( EARLIER ( 'Table'[Month] ) ) - 1
    ),
    'Table'[Value]
)

    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

v-yanjiang-msft This is excellent,thank you for your help. One last part to solve is how to get last months record value, showing against this months record. Any advice on how i would be able to calculate the last months value column?

 

v-yanjiang-msft's avatar
v-yanjiang-msft
Icon for Community Support rankCommunity Support
3 years ago

Hi Anonymous ,

It's my pleasure!

You can create another calculated column.

Last month value =
MAXX (
    FILTER (
        'Table',
        'Table'[Record ID] = EARLIER ( 'Table'[Record ID] )
            && MONTH ( 'Table'[Month] )
                = MONTH ( EARLIER ( 'Table'[Month] ) ) - 1
    ),
    'Table'[Value]
)

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.