Datediff at row level using a max date

[maurcoll]

Hi

I need some help please with a measure to calculate the difference between 2 dates where i need to show how long before the next review is due at row level. I have a list of customers and the dates they have been contacted. At a minimum they should be contacted within 6 months.
The issue is have is that customers can be contacted multiple times and how i currently have the table set up it is bringing the difference between the dates based on the row contact date i need it to show against the last contact date on all rows for the same customer.
I have created a calculated column that is a Yes, No for the latest contact date against each customer.
Days to next contact is based on the difference between today and the 6 month maximum time between contacts. In the example below the days to next contact for Customer A I would like to show as 144 in both rows. 

Customer	Date of Contact	Days to next contact
A	20/12/2024	141
A	23/12/2024	144
B	27/12/2024	148
C	29/01/2025	180

This is the current measure

VAR LastContactDate = MAX('Customer '[Date of Contact])

VAR NextContacttDate = EDATE(LastContactDate, 6)

VAR Today = TODAY()

VAR DaysRemaining = DATEDIFF(Today, NextContactDate, DAY)

RETURN

    IF(

        DaysRemaining >= 0,

        DaysRemaining & " days left",

        BLANK()

    )

[Uzi2019 · Accepted solution]

Hi @maurcoll 

Try this measure

Measure Final =

VAR LastContactDate = CALCULATE(MAX(Issue[Date of Contact]),ALLEXCEPT(Issue,Issue[Customer]))

VAR NextContactDate = EDATE(LastContactDate, 6)

VAR Today = TODAY()

VAR DaysRemaining = DATEDIFF(Today, NextContactDate, DAY)

RETURN

   IF(

        DaysRemaining >= 0,

        DaysRemaining & " days left",

        BLANK()

    )

I hope I answered your question!

[Uzi2019]

HI @maurcoll 

I have tested your measure all working fine Max date needs t be fixed with ALLEXCEPT function.  I have already given you the formula in above post. Try that..

I hope I answered your question!

[maurcoll]

That has worked, thank you

[danextian]

Hi @maurcoll 

I don't know what result you expect but if you want to compare against the latest contact date per customer, try this:

Test =
VAR LastContactDate =
    CALCULATE (
        MAX ( 'Table'[Date of Contact] ),
        ALLEXCEPT ( 'Table', 'Table'[Customer] )
    )
VAR NextContactDate =
    EDATE ( LastContactDate, 6 )
VAR Today =
    TODAY ()
VAR DaysRemaining =
    DATEDIFF ( Today, NextContactDate, DAY )
RETURN
    IF ( DaysRemaining >= 0, DaysRemaining & " days left", BLANK () )