cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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 other members find it more quickly.
Highlighted
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
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 137 members 1,796 guests
Please welcome our newest community members: