DAX Measure to calculate which number appointment it is

[Anonymous]

I have a lot of appointmets for a lot of customers and I need to create some measures based on 'What number appointment is it, for that customer'.

I have data in a table like this

Date	Customer	Appointment	Status
01-May	CMR-1	App-1	Complete
02-May	CMR-2	App-2	Complete
03-May	CMR-3	App-3	Cancelled
04-May	CMR-1	App-4	Complete
05-May	CMR-4	App-5	Complete
06-May	CMR-1	App-6	Complete
07-May	CMR-3	App-7	Complete
08-May	CMR-2	App-8	Complete

I need to know what number of appointment it is for that customer, where the appointment was completed (do not count cancelled).  For example, the # Appointments column below.

Date	Customer	Appointment	Status		# Appointment
01-May	CMR-1	App-1	Complete		1
02-May	CMR-2	App-2	Complete		1
03-May	CMR-3	App-3	Cancelled
04-May	CMR-1	App-4	Complete		2
05-May	CMR-4	App-5	Complete		1
06-May	CMR-1	App-6	Complete		3
07-May	CMR-3	App-7	Complete		1
08-May	CMR-2	App-8	Complete		2

The measures I want are

1. number of first time appointments (so a count where it is 1).  Nb. I will be slicing by month.  A person could have many appointments in a month.  I need only count the first in this measure.  If they had an appointment in a previous month, there should be nothing to count in that month.
2. Average number of appointments - Take an average across the # Appointment data

For Example

First Time Appointments	4
Average number appointments	1.57

I am working with a tabular model and was hoping to use DAX to do all of this, but I cant work out how.  Any help will be greatly appreciated 🙂

[harshnathani]

Hi @Anonymous ,

I too do not understand in the average bit.  But for the First Time Appointment you can do the following.

Create 2 Calculated Column

INDEX = CALCULATE(DISTINCTCOUNTNOBLANK(Appointments[Customer]),FILTER(Appointments,Appointments[Customer]<=EARLIER(Appointments[Customer]) ))

# Appointment = RANKX(

FILTER(

Appointments,

Appointments[Status] = "Complete" &&

Appointments[Customer] = EARLIER(Appointments[Customer])),Appointments[Appointment],,ASC)

Regards,

Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!

[Anonymous]

What does this mean?

2. Average number of appointments - Take an average across the # Appointment data

You can't just add up the numbers in the column # Appointment and divide by the number of days. It makes no sense whatsoever since this column just holds the order number of the appointments for each individual customer. And if you think it does... then I'd be glad to hear your interpretation. In reality, what you're suggesting to do is similar to this (assume that we're dealing with 1 customer only for simplicity's sake):

1. Number the rows in a table from 1 to n.

2. Take the average (1+2+...+n) / n =  n*(n+1) / (2*n) = (n+1) / 2.

What meaning does the above have? Let's see... Say n = 11 in a month (meaning there were 11 visits in a month from a single customer). The average you're suggesting is 6. So... what meaning do you attach to such a number?

I think you want to calculate something different. You want to know the average number of visits in the selected period of time but the averaging is over the customers, not days. This is a completely different calculation and it does have a well-defined meaning, namely, it answers the question: What is the average number of visits from each customer in the selected period of time?

Best

D

[AlB]

Hi @Anonymous 

For your # Appointment calculated column you can do (assuming the Date column is of date type). From that you can get to the rest

NumAppointment =
VAR currentdate_ = Table[Date]
RETURN
    CALCULATE (
        COUNT ( Table1[Appointment] ),
        ALLEXCEPT ( Table1, Table1[Customer] ),
        Table1[Date] < currendate_,
        Table1[Status] <> "Cancelled"
    )

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers