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   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 ...
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
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])
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

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

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

        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