Forum Discussion
Count values for specific date or date range
Hi Community -
I am looking to count values on a specific date / date range.
I have two tables. Date_Table and Admissions Table that are joined on Date / Admission_Date.
Admissions Table is as follows:
| Account Number | Admission Date | Discharge Date |
| 123 | 1/1/2022 | 1/3/2022 |
| 234 | 1/3/2022 | 1/5/2022 |
| 345 | 1/4/2022 | |
| 456 | 1/1/2022 | 1/2/2022 |
| 567 | 1/2/2022 | 1/4/2022 |
I am trying to count how many accounts were active for a specific date or date range. So for the sample data set above I would expect this output.
| Expected Output | |
| Date | Active Stays |
| 1/1/2022 | 2 |
| 1/2/2022 | 3 |
| 1/3/2022 | 3 |
| 1/4/2022 | 3 |
| 1/5/2022 | 2 |
| 1/6/2022 | 1 |
My current measure is not giving me my desired output:
Census =
VAR _MinDate = MIN(Date_Table[Date] )
VAR _MaxDate = MAX(Date_Table[Date] )
RETURN
COUNTROWS(
FILTER(
Admits,Admits[Date_of_Admission__c] >= _MinDate &&
Admits[Date_of_Discharge__c] <= _MaxDate )
)
Any insight on how to calculate this, or where I am making my mistake in my measure would be appreciated!
Thanks Community!
Ryan F.
8 Replies
Thank Ashish_Mathur this does not solve the issue that I had posted. This is just a basic distinctcount on an account number for a date unless I am missing something in your attached PBIX file.
See the image in my post carefully. the result there exactly matches your desired result that you posted in your initial post.
Hi ryan_b_fiting ,
You can also use Power Query to generate a row of dates when an account has been active. For example, for account 123, each date from Jan 1 to 3 will have one row in the dataset. For those that are still active, you can set them to have the dates from admission until today or whatever your end date is. Here's a sample pbix for your reference - https://drive.google.com/file/d/1BQ_bcod8EfBGCVbD8BeWPY_qrl5sqAXC/view?usp=sharing
