How to calculate the total number of reschedules appointment

DAX DAX Calculations
1

Hi All,

Have a question, i am trying to find on how to calculate the total no.of reschedule happened based on the below scenario

Reasoning: Any appointment for any customer that has been cancelled a appointment but re booked it (seen) within 1 month or 30 day period is considered rescheduled.

Example:
Rows: 8 and 9, shows a reschedule because customer 1 cancelled the appointment on 03/11/2019 but re booked it (seen) on 04/08/2019.

Customer ApptStatus Appointment Date (MM/DD/YY)
2 Seen 01-01-2018
2 Seen 01-05-2018
2 Seen 01-09-2018
2 Cancelled 01-10-2018
1 Seen 01-10-2019
1 Seen 1/23/2019
1 Cancelled 03-11-2019
1 Seen 04-08-2019
1 Seen 10-09-2019
1 Cancelled 10-10-2019
1 Seen 10-10-2019
1 Seen 10/17/2019
1 Deleted 10/27/2019
1 Seen 1/22/2020
1 Seen 02-05-2020
1 Cancelled 2/26/2020
2 Cancelled 03-10-2020
2 Seen 3/15/2020
1 Seen 8/26/2020
1 Seen 10/15/2020
1 Seen 12/28/2020
1 Seen 1/13/2021
1 Seen 3/17/2021

rescheduled sample.csv (891 Bytes)

2

Hi there,
You may solve this issue with Countax or Countrows & Filter fucntions.

image
image1859×758 55.8 KB

There is one condition in my calculation.
You can add your second condition

The following links can be useful.

https://info.enterprisedna.co/dax-function-guide/countax/
https://info.enterprisedna.co/dax-function-guide/countrows/

Good luck

3

@Nagi_k,

This one has a trickier filter context than it appears at first glance, but I think this gets you what you’re looking for:

Rescheduled = 

VAR SelCustomer = SELECTEDVALUE( 'Table'[Customer] )
VAR SelDate = SELECTEDVALUE( 'Table'[Appointment Date])
VAR SelEndDate = SELECTEDVALUE( 'Table'[OneMonthLater] )

VAR Result = 
IF(
    SELECTEDVALUE( 'Table'[ApptStatus] ) = "Cancelled",
    IF( 
        COUNTROWS(
            FILTER(
                ALL( 'Table' ),
                'Table'[ApptStatus] = "Seen" &&
                'Table'[Appointment Date] >= SelDate &&
                'Table'[Appointment Date] <= SelEndDate && 
                'Table'[Customer] = SelCustomer
            )
        )> 0,
         "Yes", "No"
    ), BLANK()
)

RETURN 
Result

image

I hope this is helpful. Full solution file attached below.

– Brian
eDNA Forum - Rescheduled Solution.pbix (19.3 KB)

4

Hi @NagiK, did the response provided by @BrianJ and @IlgarZarbaliyev help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!