DateDiff with the next availible row date

[TBSST]

Hi All, 

i am trying to run a report where i am looking at the date differences between two upload dates, based on ID's.

ID	Survey ID	Survey Date	Next Survey Required (months)
a	1	1/01/2023	2
a	2	1/03/2023	2
a	3	1/05/2023	3
a	4	1/07/2023	3
b	5	1/01/2023	2
b	6	1/04/2023	3
b	7	1/07/2023	2
b	8	1/10/2023	4
c	9	Jan-23	4
c	10	1/05/2023	1
c	11	1/07/2023	2
c	12	1/09/2023	3

i am wanting to determing how many months there was between each response per client and create a new column in the table to show the months differences. The measure would be called "Days between last Survey" For example, Client a, between survey 1 and 2, it would be 2 Months, Survey 2 and 3 would be 2 months and so on. Then Client be would be survey 5 and 6 would be 3 Months and so on. The most recent surevey dates will be from the survey date to todays date. 

From there, i would like to create a measure that identifies how many of the surveys are out of date. So if the new measure "dats between last Survey" is greater than 'next surevey required', it will be marked as 'Late'. 

Any help with this would be greatly appreciated. 

Thank you. 

[Anonymous · Accepted solution]

Hi @TBSST ,

Please try to create a measure with below dax formula:

Measure =
VAR cur_id =
    SELECTEDVALUE ( 'Table'[ID] )
VAR cur_sid =
    SELECTEDVALUE ( 'Table'[Survey ID] )
VAR cur_date =
    SELECTEDVALUE ( 'Table'[Survey Date] )
VAR required_month =
    SELECTEDVALUE ( 'Table'[Next Survey Required (months)] )
VAR next_date =
    CALCULATE (
        MAX ( 'Table'[Survey Date] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[ID] = cur_id
                && 'Table'[Survey ID] = cur_sid + 1
        )
    )
VAR month_diff =
    DATEDIFF ( cur_date, next_date, MONTH )
VAR _result =
    IF (
        ISBLANK ( month_diff ),
        IF (
            DATEDIFF ( cur_date, TODAY (), MONTH ) > required_month,
            "Late",
            "Not Late"
        ),
        IF ( month_diff > required_month, "Late", "Not Late" )
    )
RETURN
    _result

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[TBSST]

Thank you so much! This works perfectly! 

If i could continue with your support, could you also help me write the DAX to put this into a card, so it shows me the count of 'late'. 

i am also wanting a pie graph with all survey ID's, and the legend to be late/not late. 

If this something you are able to help with aswell?

[lbendlin]

Please show the expected outcome based on the sample data you provided.