Forum Discussion

Dyke211's avatar
Dyke211
Helper I
2 years ago
Solved

Need Help with date difference between values within a column

HI Guys, can anyone give me a good dax measure of calculated column that shows different in the number of days between reported events within a single column such that if i slice based on Assettype, i can see the current date different between the group of asset class in a dynamic way. That means Machine A will show me the number in days of date different between only its class type ansd Machine B will show me the date difference row by row between its own class....

 

What i haveWHat i want

 

 

 

I would like to slice by Machine type such that the days different starts from here and show me the difference between this similar type

  • tamerj1's avatar
    tamerj1
    2 years ago

    Hi Dyke211 

    please try

    Number of Days Diff. =
    VAR CurrentDate = 'Table'[ReportedEvent]
    VAR CurrentTypeDates =
        CALCULATETABLE (
            VALUES ( 'Table'[ReportedEvent] ),
            ALLEXCEPT ( 'Table', 'Table'[AssetType] )
        )
    VAR previousDate =
        MAXX (
            FILTER ( CurrentTypeDates, 'Table'[ReportedEvent] < CurrentDate ),
            'Table'[ReportedEvent]
        )
    RETURN

        DATEDIFF ( COALESCE ( previousDate, CurrentDate ), CurrentDate, DAY )

2 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    2 years ago

    Hi Dyke211 

    please try

    Number of Days Diff. =
    VAR CurrentDate = 'Table'[ReportedEvent]
    VAR CurrentTypeDates =
        CALCULATETABLE (
            VALUES ( 'Table'[ReportedEvent] ),
            ALLEXCEPT ( 'Table', 'Table'[AssetType] )
        )
    VAR previousDate =
        MAXX (
            FILTER ( CurrentTypeDates, 'Table'[ReportedEvent] < CurrentDate ),
            'Table'[ReportedEvent]
        )
    RETURN

        DATEDIFF ( COALESCE ( previousDate, CurrentDate ), CurrentDate, DAY )