Finding overdue surveys by using previous date in column

[TBSST]

Hi All, 

This may seem like a repeat of a question i had before, however I think what i supplied did not reflect what i was trying to achive. I am trying to find surveys that were not completed within 3 months. I am dertmining this by using the date completed column. i am using the date, and determining if the previous complete date from the same ID is within 3 months.  

ID	SurveyID	Date Compelted
a	7709	20/05/2022
a	8066	18/10/2022
a	8578	21/02/2023
a	10323	5/07/2023
b	8644	22/02/2023
b	10247	27/06/2023
c	8790	1/03/2023
c	10192	9/06/2023
d	8531	7/02/2023
d	8961	8/05/2023
d	10595	8/08/2023

I am wanting to be able to add another column showing how many months were in between each survey. The first date being marked as "first" as there is no date to go off prior to the first one. An example of what i want is: 

ID	SurveyID	Date Compelted	Months since last due
a	7709	20/05/2022	First
a	8066	18/10/2022	4
a	8578	21/02/2023	4
a	10323	5/07/2023	5
b	8644	22/02/2023	First
b	10247	27/06/2023	4
c	8790	1/03/2023	First
c	10192	9/06/2023	4
d	8531	7/02/2023	First
d	8961	8/05/2023	3
d	10595	8/08/2023	3

The second part is i would like a card to identify how many overdue surveys there were. So for the sample above, there would be 4 overdue surveys. 

thank you for your support. 

[Anonymous · Accepted solution]

Hi @TBSST ,

Based on your description, I created the sample with dd/mm/yyyy and got the following results:

I calculated it with 30 days as a month, and some of the results I got were a bit inconsistent with what you expected, so please correct me if I misunderstood your need!

DAX for column:

Previous =
var CurrentSurveyID = Survey[SurveyID]
var _pre = CALCULATE(
MAX(Survey[Date Compelted]),
FILTER(Survey, Survey[SurveyID] < CurrentSurveyID && Survey[ID] = EARLIER(Survey[ID]))
)
return
ROUND(DATEDIFF(_pre,'Survey'[Date Compelted],DAY)/30,0)

An attachment for your reference. Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[DallasBaba]

@TBSST you can create a calculated column to find the previous completion date:

Previous Completion Date =
VAR CurrentSurveyID = Survey[SurveyID]
RETURN
CALCULATE(
MAX(Survey[Date Completed]),
FILTER(Survey, Survey[SurveyID] < CurrentSurveyID && Survey[ID] = EARLIER(Survey[ID]))
)

--- Create another calculated column to find the months since the last due date:

Months Since Last Due =
VAR PrevCompletionDate = [Previous Completion Date]
VAR CurrentDate = Survey[Date Completed]
RETURN
IF(
ISBLANK(PrevCompletionDate),
"First",
IF(
DATEDIFF(PrevCompletionDate, CurrentDate, MONTH) > 3,
"Overdue",
DATEDIFF(PrevCompletionDate, CurrentDate, MONTH)
)
)

--- create a measure to count the overdue surveys:

Overdue Surveys =
CALCULATE(
COUNTROWS(Survey),
FILTER(Survey, [Months Since Last Due] = "Overdue")
)

Now, you can use two calculated columns and measure in your  visuals:

Drag the "ID" and "SurveyID" columns into a table or matrix visual.
Use the "Date Completed" column for the date axis if needed.
Drag the "Months Since Last Due" calculated column to a visual to see how many months have passed since the last survey.
Use the "Overdue Surveys" measure in a card visual to display the count of overdue surveys.

Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Let me know if this work
@ me in replies or I'll lose your thread!!!