Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

First date attribute seen within sequence

I have the following asset condition data

 

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

 

AssetIDDateCondition
A12301/01/2019  2
B45601/01/2022  4

 

Many thanks for any help you can give

Need Help
  • johnt75's avatar
    johnt75
    4 years ago
    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

6 Replies

  • johnt75's avatar
    johnt75
    Super User
    4 years ago
    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        johnt75
        Super User
        4 years ago

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