cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
pe2950 Regular Visitor
Regular Visitor

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

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

bi-2.jpg

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? BI-1.jpg
2 REPLIES 2
Community Support Team
Community Support Team

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

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.

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
pe2950 Regular Visitor
Regular Visitor

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

@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. 

 

 

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors