Forum Discussion

PaulStaps's avatar
PaulStaps
Frequent Visitor
7 years ago

Change in Values over Time - Calculated Column?

I have a table where ID's are changing over time for each client and and I want to display in a table on a report the change from original and most recent ID as shown below.  It would only ever need to show the original ID v the most recent ID as there wouldn't be multiple changes.  I've managed to pull the IDs into new columns using DAX for the max and min dates but I can't work out how to display those values in a table in a visual.  Any ideas?

 

(if(CALCULATE(MAX('Table A'[DATE]),FILTER('Table A','Table A'[CLIENT]='Table A'[CLIENT]))='Table A'[DATE],'Table A'[ID],BLANK()))

 

Table A

CLIENT   ID        DATE

AAA       123      1/1/19 

AAA       123      3/1/19

AAA       456      1/2/19

 

Table Visual

Original       New

123              456

 

Thanks

Need Help

3 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    7 years ago

    PaulStaps 

     

    Try these Measures

     

    Measure(NewID) = CALCULATE(max(TableName[ID]),LASTDATE(TableName[DATE]))

Measure(OldID) = CALCULATE(max(TableName[ID]),FIRSTDATE(TableName[DATE]))
    • PaulStaps's avatar
      PaulStaps
      Frequent Visitor
      7 years ago

      Thanks Zubair, the problem with that is that it doesn't work in a table unless you include the ID aswell.  What I wanted ideally was to reflect just the original and new IDs in a table.  Any thoughts?

      • v-piga-msft's avatar
        v-piga-msft
        Resident Rockstar
        7 years ago

        Hi PaulStaps ,

        It seems that the measure provided by Zubair_Muhammad could achieve your desired output like below.

        If you still need help, please share the screenshot to describe your issue in details so that we could understand better.

        Best Regards,

        Cherry