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.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hello, thamks for the response. Where would I insert COALESCE ?

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))

 

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
4 years ago

whereever your measure returns BLANK()

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    I just guessing here, but believe the blanks are in both variables that use EARLIER because there is only one date for the records that return blank, for example where would coalesce go 

    CALCULATE(MAX('Table'[Date]),FILTER(ALL('Table'), 'Table'[Date]<_maxdate && 'Table'[ID]=EARLIER('Table'[ID])))

    do I wrap coalesce like COALESCE( CALCULATE(MAX('Table'[Date]),FILTER(ALL('Table'), 'Table'[Date]<_maxdate && 'Table'[ID]=EARLIER('Table'[ID]))))

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thanks Ibendlin, COALESCE() worked like so,

      COALESCE( CALCULATE(MAX('Table'[Date]),FILTER(ALL('Table'), 'Table'[Date]<_maxdate && 'Table'[ID]=EARLIER('Table'[ID])))),'Table'[Date])