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 and I would like to identify when there has been changes to key data fields between months.

 

Example shown below: Blue Columns represent the Appended Query and im looking for a formula that would work to replicate the Orange Columns.

Can anyone help! Thanks!

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.

     

7 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      amitchandak Afraid not. The data structure I am okay with. This will involve appending Monthly versions of an Excel. I'm struggling when attempting to lookup what the previous month was against the same ID to understand if a change has been made.

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

    Hi Anonymous ,

    According to your description, here's my soluton, create a calculated column.

    Change =
VAR _Pre =
    MAXX (
        FILTER (
            'Table',
            'Table'[Record ID] = EARLIER ( 'Table'[Record ID] )
                && MONTH ( 'Table'[Month] )
                    = MONTH ( EARLIER ( 'Table'[Month] ) ) - 1
        ),
        'Table'[Value]
    )
RETURN
    IF ( _Pre = BLANK (), "N/A", IF ( [Value] = _Pre, "N", "Y" ) )

    Get the correct 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.

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    v-yanjiang-msft - Looking for a bit more advice on this!

     

    The code provided seems to work however as its based on Months only, in January (01) its not finding December (12) as i think the formula is looking at month number only.

     

    Is there any way of expanding this to work for Year as well?

     

    Thanks!