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 dat...
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!

     

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