Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 extracted. The latest data is identified with column called "Is Current" and we also have the date this data was extracted.
I would like to have a column which identifies if this milestone has changed and if so, what is the current value compared to the last.
Solved! Go to Solution.
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.
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.
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
Use COALESCE to fill in a meaningful replacement for missing data.
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))
whereever your measure returns BLANK()
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]))))
Thanks Ibendlin, COALESCE() worked like so,
COALESCE( CALCULATE(MAX('Table'[Date]),FILTER(ALL('Table'), 'Table'[Date]<_maxdate && 'Table'[ID]=EARLIER('Table'[ID])))),'Table'[Date])
Perfect! Thank you very much 🙂
Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to assist you. Avoid posting screenshots of your source data if possible.
Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
95 | |
77 | |
65 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |