Comparing GroupedBy Dates

[czuniga]

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/2020
555	2/7/2020
888	2/8/2020
999	2/9/2020
999	2/10/2020
999	2/11/2020

I'm trying to figure out how to keep track of the success rate of scheduling customers next appointment when they visit.  Successful Dataset would look like this (1 = NextAptScheduled): 

Customer ID	AptDate (Past & Future)	NextAptScheduled
111	1/1/2019	1
111	5/1/2021	0
222	2/3/2020	0
444	2/4/2020	0
555	2/5/2020	1
555	2/6/2020	1
555	2/7/2020	0
888	2/8/2020	0
999	2/9/2020	1
999	2/10/2020	1
999	2/11/2022	0

I'm thinking it makes sense to group the customers by their ID's with the dates of their purchases, but am having a hard time figuring if I should do that with a groupby, summarize,  or summarizecolumns, and how to work with the dates in their respective groups to achieve the goal from there. 

Any help would be greatly appreciated. Thank you! 

[Ashish_Mathur · Accepted solution]

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]

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

[PhilipTreacy]

Hi @czuniga 

Please supply some sample data and your expected result so we can see what we're working with.

Regards

Phil

[czuniga]

Thanks for the suggestion. Done! 

[amitchandak]

@czuniga , expected output is not clear . But these columns can help

rank Column for dates = rankx(filter(Table, [Customer] = earlier([customer]) ), [Date],,asc)

last purcahse date date = maxx(filter(Table, [Customer] = earlier([customer]) && [Date] = earlier([Date])),[Date])

[czuniga]

Tried again to explain what I'm trying to accomplish as I wasn't able to get what you suggested work. Appreciate the input though!