Forum Discussion

dswallow's avatar
dswallow
Icon for Helper I rankHelper I
4 years ago
Solved

Identify if value has changed over time with same ID

Hi,   I am trying to identifty if my milestone date value has changed by looking at the latest value compared to previous value?   A new row is created each month when the milestone data is extra...
Need Help
Show and Tell
  • v-xiaotang's avatar
    v-xiaotang
    4 years ago

    Hi dswallow 

    Thanks for reaching out to us.

    You can try this, create the column below

    Milestone changed and value = 
var _maxdate= CALCULATE(MAX('Table'[Date]),FILTER(ALL('Table'),'Table'[ID]=EARLIER('Table'[ID])))
var _predate= CALCULATE(MAX('Table'[Date]),FILTER(ALL('Table'), 'Table'[Date]<_maxdate && 'Table'[ID]=EARLIER('Table'[ID])))
var _cur='Table'[Milestone]
var _pre= CALCULATE(MAX('Table'[Milestone]),FILTER(ALL('Table'),'Table'[ID]=EARLIER('Table'[ID]) && 'Table'[Date]= _predate))
return IF('Table'[Date]=_maxdate,IF(_cur=_pre,"No change", "Changed, pre="&_pre&", cur="& _cur))

    result

     

    Best Regards,

    Community Support Team _Tang

    If this post helps, please consider Accept it as the solution to help the other members find it more quickly.