Counting rows by day/week/month across two date columns within one table?

[pe2950]

I'm trying to setup visuals that graph the count of appointments, counting number of rows by day, by week, and by month. 

Each appointment is represented as a row in my dataset, and each record has 2 date columns. One indicates the appointmet date, the other indicates the created date. 

A row will look something like this: {ID, AppointmentDate, CreateDate, Status, UserID, etc}

I created a datekey table, and create a relationship between the date and the created date of the record. I then can use the date, week_starting, and month_starting from the date key and then a simple count on the rows to display the visual. 

Measure i use getting rows created by day:

_day = COUNT(Consultations[create_teimstamp])+0

To generate data for week and month i just count the number of rows returned with "WeekStarting" or "month starting" from my date table and then apply some relative filtures on the date column. 

I can't sort out how to duplicate the same thing based off of the appointment date for the 2nd set of reports, since i already have a relationship between my date table and my appointment date across the created date column. 

• Do i have to duplicate the appointment table and then create the relationship from this duplicated table? (it's a lot of data, so i'd rather not)
• It seems like i need to duplicate all work across another column, this seems needlessly complex. Is there a simplier way of doing this? 

[v-frfei-msft]

Hi @pe2950 ,

Could you please share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

[pe2950]

@v-frfei-msft 

Sample of data with confidential information removed: https://app.box.com/s/cnjwyp04taz1q3w1pm4a4rdtl63ptgsl

-Each row is a unique appointment

-Each row has a create_timestamp & appointment_date

-Each row has a status, so for example if an appointment for person-a is rescheduled there will be 2 rows in the data, with the resched_ind flag set to indicate the new appointment is a rescheduled and then it is linked back to the GUID of the original record. 

I'm trying to graph across two different date fields:

Appointments created by day

appointments created by week

appointments created by month

Appointments kept by day

appointments kept by week

appointments kept by month

A kept appointment will use the appt_date column and the status of kept 

What i've done that seems to be working is create 2 relationships between Date table and Appopintment Table, one on the create_timestamp another on the appointment_date. 

I then created measures:

Count_AppointmentTimestamp = CALCULATE(COUNT(Consultations[appointmentDate])+0, USERELATIONSHIP(DateKey[Date],Consultations[appointmentDate]))

This seems to be working, but is tedious to do this across the different outputs of day week months, etc.