cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rfickes Frequent Visitor
Frequent Visitor

Calculating Census by Month / Week using ID, Admit Date, and Discharge Date

I have three columns of data: client_id, admit_date, and discharge_date. From this, I need to be able to calculate how many clients are in the program on any given day and be able to find average daily census numbers for a time period (week and month).

 

It seems to me that I should be able to do a form of COUNTIF of distinct client_id if the start_date is <= last day of the reporting period and the discharge date is >= first day of the reporting period, but I have no idea how to actually do that in a calculated measure.

 

Any help guiding me toward the right path would be greatly appreciated.

8 REPLIES 8
Community Support Team
Community Support Team

Re: Calculating Census by Month / Week using ID, Admit Date, and Discharge Date

Hi @rfickes,

 

Can you please share some sample data to test?

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
rfickes Frequent Visitor
Frequent Visitor

Re: Calculating Census by Month / Week using ID, Admit Date, and Discharge Date

@v-shex-msft

 

I'm not entirely sure how to do that, but the data is pretty simple.

 

client_idadmit_datedischarge_date
1 2016-10-21 2017-04-17
2 2016-10-27 2017-03-26
3 2016-11-01 2017-03-09
4 2016-11-02 2017-03-14
5 2016-11-15 2017-05-06
6 2016-12-01 2017-04-10
7 2016-12-02 2017-04-10
8 2016-12-04 2017-05-26
9 2016-02-13 2017-05-06
10 2016-12-08 2017-05-17
11 2016-12-14 2017-03-28
12 2016-12-16 2017-03-09
13 2016-12-16 2017-03-18
14 2016-12-18 2017-03-30
15 2016-12-19 2017-03-05
16 2016-12-22 2017-05-08
17 2016-12-30 2017-04-21
18 2017-01-01 2017-03-07
19 2017-01-04 2017-05-18
20 2017-01-09 2017-03-29

 

... and so on for a couple thousand rows.

Iadem Frequent Visitor
Frequent Visitor

Re: Calculating Census by Month / Week using ID, Admit Date, and Discharge Date

i think you need something like the measure
=CALCULATE(SUM([id]);DATESBETWEEN('Таблица1'[date 2];FIRSTDATE('Таблица1'[date 2]);LASTDATE('Таблица1'[date 2])))

Or you can use columns (=weeks(); and =month()), add them to slicer and use the measure AVG

Sorry for my English 😉

Community Support Team
Community Support Team

Re: Calculating Census by Month / Week using ID, Admit Date, and Discharge Date

Hi @rfickes,

 

According to your description, you want to get the distinct client count of selected date range, right?

If this is a case, you can refer to below formula to calculate the distinct count of match client count.

 

Steps:

1. Create a calendar with original date.

Calendar = CALENDAR(FIRSTDATE(Sheet2[admit_date]),LASTDATE(Sheet2[discharge_date])) 

 

2. Add a measure to original table to calculate based on select range on calendar table.

Count = CALCULATE(DISTINCTCOUNT(Sheet2[client_id]),FILTER(ALL(Sheet2),[admit_date]>=FIRSTDATE(ALLSELECTED('Calendar'[Date]))&&[discharge_date]<=LASTDATE(ALLSELECTED('Calendar'[Date]))))

 

3. Use calendar date as the source of slicer, then calculate with selected date.

4.PNG5.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
rfickes Frequent Visitor
Frequent Visitor

Re: Calculating Census by Month / Week using ID, Admit Date, and Discharge Date

Is there any way to get around FIRSTDATE and LASTDATE not being usable in DirectQuery mode?

Community Support Team
Community Support Team

Re: Calculating Census by Month / Week using ID, Admit Date, and Discharge Date

Hi @rfickes,

 

You can try to turn on below option.

15.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
Highlighted
rfickes Frequent Visitor
Frequent Visitor

Re: Calculating Census by Month / Week using ID, Admit Date, and Discharge Date

Allowing unrestricted measures in DirectQuery mode solved one of the issues, but is it possible to build the calendar in DirectQuery and not Import mode?

 

I'm using DirectQuery to read the data from a SQL data warehouse and avoid having to pull over tens of thousands of lines of data into Power BI, but it's really starting to hamper my ability to work with the data. That being said, I'm new to Power BI and data warehousing, so I may be missing something obvious.

 

According to your description, you want to get the distinct client count of selected date range, right?

Actually, that's close, but I'm looking to be able to get an average distinct client count per day over a defined date range. So, for example, let's say on Monday I have 350 clients, on Tuesday I have 375, on Wendesday I have 365, and on Thursday I have 380. For Monday, my average daily client count would be 350. For Monday - Tuesday, it would be 362.5. For the week, it would be 367.5.

Community Support Team
Community Support Team

Re: Calculating Census by Month / Week using ID, Admit Date, and Discharge Date

HI @rfickes,

 

>>Allowing unrestricted measures in DirectQuery mode solved one of the issues, but is it possible to build the calendar in DirectQuery and not Import mode?

You can try to create it in power query:

Dynamic Calendar Table

 

Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |

Helpful resources

Announcements
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 Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

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: 329 members 3,240 guests
Please welcome our newest community members: