topic Re: Dax help Max date with exclusions in DAX Commands and Tips

Dax help Max date with exclusions

GJD23 — Thu, 16 Jan 2025 14:11:35 GMT

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

Re: Dax help Max date with exclusions

johnt75 — Thu, 16 Jan 2025 14:36:21 GMT

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

Re: Dax help Max date with exclusions

GJD23 — Thu, 16 Jan 2025 14:54:49 GMT

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

Re: Dax help Max date with exclusions

johnt75 — Thu, 16 Jan 2025 15:03:41 GMT

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.

Re: Dax help Max date with exclusions

GJD23 — Thu, 16 Jan 2025 15:29:42 GMT

Thanks John, I'll have a play with that

Re: Dax help Max date with exclusions

GJD23 — Thu, 16 Jan 2025 16:00:58 GMT

AWESOME - it works thank you so much

Re: Dax help Max date with exclusions

GJD23 — Thu, 16 Jan 2025 16:14:12 GMT

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