Forum Discussion

GJD23's avatar
GJD23
Frequent Visitor
1 year ago
Solved

Dax help Max date with exclusions

Hi everyone - bit stuck and looking for some help.   I have daily files showing purchase reqs all rolled in to a big data set. So a req will appear each day ( and the req age will increase) each da...
  • johnt75's avatar
    johnt75
    1 year ago

    I think you could create a calculated column like

    Final Age =
VAR LatestDate =
    CALCULATE ( MAX ( 'Table'[Date] ), REMOVEFILTERS () )
VAR AllReqs =
    ALL ( 'Table'[Req ID] )
VAR LatestReqs =
    CALCULATETABLE (
        DISTINCT ( 'Table'[Req ID] ),
        REMOVEFILTERS (),
        'Table'[Date] = LatestDate
    )
VAR ValidReqs =
    EXCEPT ( AllReqs, LatestReqs )
VAR Result =
    IF (
        'Table'[Req ID] IN ValidReqs,
        VAR StartDate =
            CALCULATE ( MIN ( 'Table'[Date] ), ALLEXCEPT ( 'Table', 'Table'[Req ID] ) )
        VAR EndDate =
            CALCULATE ( MAX ( 'Table'[Date] ), ALLEXCEPT ( 'Table', 'Table'[Req ID] ) )
        VAR Result =
            DATEDIFF ( StartDate, EndDate, DAY )
        RETURN
            Result
    )
RETURN
    Result
johnt75's avatar
johnt75
Icon for Super User rankSuper User
1 year ago

I think you could create a calculated column like

Final Age =
VAR LatestDate =
    CALCULATE ( MAX ( 'Table'[Date] ), REMOVEFILTERS () )
VAR AllReqs =
    ALL ( 'Table'[Req ID] )
VAR LatestReqs =
    CALCULATETABLE (
        DISTINCT ( 'Table'[Req ID] ),
        REMOVEFILTERS (),
        'Table'[Date] = LatestDate
    )
VAR ValidReqs =
    EXCEPT ( AllReqs, LatestReqs )
VAR Result =
    IF (
        'Table'[Req ID] IN ValidReqs,
        VAR StartDate =
            CALCULATE ( MIN ( 'Table'[Date] ), ALLEXCEPT ( 'Table', 'Table'[Req ID] ) )
        VAR EndDate =
            CALCULATE ( MAX ( 'Table'[Date] ), ALLEXCEPT ( 'Table', 'Table'[Req ID] ) )
        VAR Result =
            DATEDIFF ( StartDate, EndDate, DAY )
        RETURN
            Result
    )
RETURN
    Result