First date attribute seen within sequence

[Anonymous]

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
A123	01/01/2018	4
A123	01/01/2019	2
A123	01/01/2020	2
A123	01/01/2021	2
A123	01/01/2022	2
B456	01/01/2013	1
B456	01/01/2014	1
B456	01/01/2015	1
B456	01/01/2016	2
B456	01/01/2017	2
B456	01/01/2018	2
B456	01/01/2019	3
B456	01/01/2020	3
B456	01/01/2021	3
B456	01/01/2022	4

I need to find the first date the latest condition was found for each asset. But the query also needs to understand the sequence of the surveys.

For A123 its current condition is 2. It was condition 2 in 2013, it got worse to condition 4, was repaired and is now condition 2 again. I need to find 01/01/2019 - the first time it became condition 2 in the current sequence 

The resulting table should look like this

AssetID	Date	Condition
A123	01/01/2019	2
B456	01/01/2022	4

Many thanks for any help you can give

[johnt75 · Accepted solution]

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 

[Anonymous]

Hi,

Thanks for your repsonse. I'm getting a circular dependency when I add the table name and paste that DAX

Earliest Current Condition Date =

var currentAsset = SELECTEDVALUE('Sheet2'[AssetID])

var currentCondition = SELECTCOLUMNS( CALCULATETABLE( TOPN( 1,'Sheet2', 'Sheet2'[Date], DESC),'Sheet2'[AssetID] = currentAsset), "@condition", 'Sheet2'[Condition] )

var prevConditionDate = CALCULATE( MAX('Sheet2'[Date]), REMOVEFILTERS('Sheet2'), 'Sheet2'[AssetID] = currentAsset && 'Sheet2'[Condition] <> currentCondition )

var result = CALCULATE( MIN('Sheet2'[Date]),REMOVEFILTERS('Sheet2'), 'Sheet2'[AssetID] = currentAsset && 'Sheet2'[Condition] = currentCondition && 'Sheet2'[Date] > prevConditionDate )

return result

<pi>A circular dependency was detected: Sheet2[Earliest Current Condition Date], 1e517ef3-3123-4a08-a46c-73704fa051fc, Sheet2[Earliest Current Condition Date].</pi>

How should I be using the code and any ideas what the issue is? Once I've got the first date i've got a few other steps so ideally i'd do this in Power Query if its possible?

[johnt75]

Its meant to be a measure, not a column. If you create it as a measure you should be able to add it to a table visual along with the asset ID column and it will show the current values for each asset.

If you needed to refer to it in other calculations then you could either calculate it on the fly or create a calculated table storing the asset ID and the current earliest condition date using the below code

Summary Table = SUMMARIZECOLUMNS( 'Sheet2'[AssetID], "Earliest Date", [Earliest Current Condition Date])

[Anonymous]

Is it possible to do this type of thing in Power Query? Once I've determined the first date I then have a workflow which combines the asset data with deteriortion and costing model data. It merges and pivots the data around to work out how long the asset has left and how much its going to cost. Thats mostly created in Power Query

[johnt75]

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.

[Anonymous]

Thank you. With your explanation and working through the logic of the DAX you provided i've replicated the steps in Power Query. 

1. Group by Asset ID to give Max Date per asset
2. Using Max Date and Asset ID determine latest condition
3. Merge this back to table on Asset ID and Date
4. If statement to determine which dates are, and which are not the current condition
5. Filter down to those that are not the current condition
6. Group by Asset ID to give Max non current date per asset
7. Merge this back to table on Asset ID
8. If statement to determine which dates are greater than Max non current date
9. Filter to just those that are greater
10. Group by Asset ID to give minimum date per asset