Forum Discussion
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 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
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
6 Replies
johnt75Super User
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 ResultPlease 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 ResultThe 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.
Thanks John, I'll have a play with that
AWESOME - it works thank you so much
It works John - thank you so much - i thought I had uncovered a blip but as usual it was my bad
