Forum Discussion
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...
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
