Forum Discussion
First date attribute seen within sequence
I have the following asset condition data AssetID Date Condition A123 01/01/2013 2 A123 01/01/2014 2 A123 01/01/2015 3 A123 01/01/2016 3 A123 01/01/2017 4 ...
Earliest Current Condition Date =
var currentAsset = SELECTEDVALUE('Table'[AssetID])
var currentCondition = SELECTCOLUMNS( CALCULATETABLE( TOPN( 1,'Table', 'Table'[Date], DESC),'Table'[AssetID] = currentAsset),
"@condition", 'Table'[Condition] )
var prevConditionDate = CALCULATE( MAX('Table'[Date]), REMOVEFILTERS('Table'), 'Table'[AssetID] = currentAsset && 'Table'[Condition] <> currentCondition )
var result = CALCULATE( MIN('Table'[Date]),REMOVEFILTERS('Table'),
'Table'[AssetID] = currentAsset && 'Table'[Condition] = currentCondition && 'Table'[Date] > prevConditionDate )
return result
You could probably do a bunch of transformations and turn it into a custom function, but my Power Query isn't good enough to write the code.
The basic principle is to find the max date where the condition is different to the current value, then find the min date which is greater than that and the condition is the same as the current value.
Thank you. With your explanation and working through the logic of the DAX you provided i've replicated the steps in Power Query.
- Group by Asset ID to give Max Date per asset
- Using Max Date and Asset ID determine latest condition
- Merge this back to table on Asset ID and Date
- If statement to determine which dates are, and which are not the current condition
- Filter down to those that are not the current condition
- Group by Asset ID to give Max non current date per asset
- Merge this back to table on Asset ID
- If statement to determine which dates are greater than Max non current date
- Filter to just those that are greater
- Group by Asset ID to give minimum date per asset