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!

     

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!

     

     

    • maurcoll's avatar
      maurcoll
      Helper IV
      1 year ago

      That has worked, thank you