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 IDAptDate
1111/1/2019
1111/5/2019
2222/3/2020
4442/4/2020
5552/5/2020
5552/6/2020
5552/7/2020
8882/8/2020
9992/9/2020
9992/10/2020
9992/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 IDAptDate (Past & Future)NextAptScheduled
1111/1/20191
1115/1/20210
2222/3/20200
4442/4/20200
5552/5/20201
5552/6/20201
5552/7/20200
8882/8/20200
9992/9/20201
9992/10/20201
9992/11/20220

 

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! 

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.

6 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    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's avatar
      czuniga
      Helper III
      5 years ago

      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! 

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    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.

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    5 years ago

    Hi czuniga 

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

    Regards

    Phil

  • 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