Forum Discussion

maurcoll's avatar
maurcoll
Helper IV
1 year ago
Solved

Datediff at row level using a max date

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. 

CustomerDate of ContactDays to next contact
A20/12/2024141
A23/12/2024144
B27/12/2024148
C29/01/2025180


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()
    )
Need Help
  • Uzi2019's avatar
    Uzi2019
    1 year ago

    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!

     

4 Replies

  • Uzi2019's avatar
    Uzi2019
    Community Champion
    1 year ago

    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's avatar
      Uzi2019
      Community Champion
      1 year ago

      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!

       

       

  • danextian's avatar
    danextian
    Super User
    1 year ago

    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 () )