Forum Discussion

czuniga's avatar
czuniga
Helper III
5 years ago
Solved

Comparing GroupedBy Dates

I have a list of customers and the dates of their appointments like so:   Customer ID AptDate 111 1/1/2019 111 1/5/2019 222 2/3/2020 444 2/4/2020 555 2/5/2020 555 2/6/20...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    5 years ago

    Hi,

    This calculated column formula works

    =1*(not(CALCULATE(MAX(Data[AptDate (Past & Future)]),FILTER(Data,Data[Customer ID]=EARLIER(Data[Customer ID])))=Data[AptDate (Past & Future)]))

    Hope this helps.

mahoneypat's avatar
mahoneypat
Microsoft Employee
5 years ago

You can use this expression for a new Column on your Appointments table (replace with your actual table name).  I added a comment too in case you need the measure version in a table visual with your customer ID and Date columns.

 

NextApptScheduled =
VAR vThisDate = Appointments[AptDate]  //use MIN(Appointments[AptDate]) if a measure is needed instead
RETURN
    IF (
        ISBLANK (
            CALCULATE (
                COUNTROWS ( Appointments ),
                ALLEXCEPT (
                    Appointments,
                    Appointments[Customer ID]
                ),
                Appointments[AptDate] > vThisDate
            )
        ),
        0,
        1
    )

 

Pat