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
GJD23's avatar
GJD23
Frequent Visitor
1 year ago

Please excuse  me if i'm totally off here but i'm thinking the last part is trying to calculate the age whereas there is a column in the data " Req Age" that shows the age each day, so i just need the max ( but only if it is complete, otherwise clock is still ticking and i don't want to include in number - trying to under stand if the other part filter out all the req numbers tyhat are still showing in todays data, which will take me a while to follow 
Many thanks for your help, i'll examine further



 

johnt75's avatar
johnt75
Icon for Super User rankSuper User
1 year ago

If you already have the req age column then that makes things simpler.

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 Result =
            CALCULATE ( MAX ( 'Table'[Req Age] ), ALLEXCEPT ( 'Table', 'Table'[Req ID] ) )
        RETURN
            Result
    )
RETURN
    Result

The code calculates the latest date in all the data, then retrieves all values for the req ID and the values for the req ID which have an entry on the latest date. It excludes the ones with the latest date from the list of all of them and checks to see if the current value for req ID exists in the remainder. If it exists, then it gets the max value for req age for that req ID, otherwise it returns blank.