Forum Discussion

Helper 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

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.

Identify if value has changed over time with same ID.pbix46 KB

v-xiaotang

Community Support

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.

Identify if value has changed over time with same ID.pbix46 KB

Anonymous

Not applicable

4 years ago

Hello, is there a way to provide a second date value for the records that only have one date? possibly using a date table or DateNow(). or have the value of No Change when the record does not have 2 dates.

for the records with only one date, my result shows "Changed, Pre=,Cur=MyValue" I'm guessing it's showing Changed because it's comparing Pre(no value) to Cur(has a value).

otherwise it works perfect for my data, thanks

lbendlin

Super User

4 years ago

Use COALESCE to fill in a meaningful replacement for missing data.

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
Super User
4 years ago
whereever your measure returns BLANK()