Dax help Max date with exclusions

[GJD23]

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 day until it is converted to a PO, at which point it will no longer show.

I need to work out the final age of each req, or how long it took to convert to a PO

So i want to discount all "Pucrchase Req" numbers that appear in the latest days data ( as these are still open) and then retrieve the MAX "REqAge" for all the others and ultimatley calculate %age that are less than or equal to 14 (days) and those greater.

Can't quite get my head around it - filter out all Purchase Req numbers that appear in the latest days data

Any help appreciated 

[GJD23]

It works John - thank you so much - i thought I had uncovered a blip but as usual it was my bad

[GJD23]

Thanks John, I'll have a play with that

[GJD23]

AWESOME - it works thank you so much

[GJD23]

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]

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.

[johnt75 · Accepted solution]

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