Find max date within groups and count unique records if date is overdue

[AMZA]

Hello everyone,

I am looking to calculate number of unique people (ID) who's review date is overdue. And I am looking for dynamic formula which calculates on the fly and does not pick column D or E in calculation (as I have date based filters)

The calculation goes as follows:

- Get latest joining or review date for each person (Max of column B or C) = Answer column D

- Calculate next review date i.e. column D plus 1 month = Answer column E

- Calculate number of people who's date is overdue i.e. column F = Answer 3

As mentioned please suggest dynamic formula as rows can vary based on filter section. I spent couple of hours around this but couldn't achieve desired result. Thanks for your help

For reporting today's date is: 10-Feb-23

A	B	C	D	E	F
ID	Joiningdate	Reviewdate	LatestJoining&ReviewDate	NextReviewDate (+1 month)	Overdue
1	11-Jan-23		11-Jan-23	11-Feb-23
2	01-Jan-23	02-Jan-23	07-Jan-23	07-Feb-23	Yes
2	01-Jan-23	04-Jan-23	07-Jan-23	07-Feb-23	Yes
2	01-Jan-23	07-Jan-23	07-Jan-23	07-Feb-23	Yes
3	15-Dec-22	18-Dec-22	25-Dec-22	25-Jan-23	Yes
3	15-Dec-22	25-Dec-22	25-Dec-22	25-Jan-23	Yes
4	01-Dec-22	15-Dec-22	20-Dec-22	20-Jan-23	Yes
4	01-Dec-22	20-Dec-22	20-Dec-22	20-Jan-23	Yes
5	01-Dec-22	12-Jan-23	12-Jan-23	12-Feb-23
				Answer	3 people

[AMZA]

Hi Pat,

This seems to be in right direction. I was looking for logic flow and your answer makes sense. I will make date dynamic and then it should be good. 

Thanks

[ppm1 · Accepted solution]

Here's one way to do it in a measure expression that only needs your ID, JoiningDate and ReviewDate columns. I added count of ID column just to show the rows that don't get counted. See correct value of 3 in total (not sure what fields you plan to use in your visual). Note that I used a hardcoded date as requested, but you could use a dynamic one easily too.

People Overdue =
VAR ac =
    ADDCOLUMNS (
        T1,
        "cNextReview", EDATE ( MAX ( T1[Joiningdate], T1[Reviewdate] ), 1 )
    )
VAR overdue =
    FILTER ( ac, [cNextReview] < DATE ( 2023, 2, 10 ) )
RETURN
    COUNTROWS ( SUMMARIZE ( overdue, T1[ID] ) )

Pat