cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ryan_b_fiting
Helper V
Helper V

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 NumberAdmission DateDischarge Date
1231/1/20221/3/2022
2341/3/20221/5/2022
3451/4/2022 
4561/1/20221/2/2022
5671/2/20221/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
DateActive 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 8
danextian
Super User
Super User

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 

danextian_0-1654569338038.png

 






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa
Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Ok, I see it now in Power Query. 

When I apply the in Power Query and try to refresh my data I get an OLE DB ODBC Error 'Increment' argument is out of range.  My data is from salesforce, once I get this error figured out, I have one other questio: how scalable is this solution?  My table already has 40k+ rows and some with stays 30 days or more?  Creating multiple lines for each unique stay seems like this is going to cause performance issues sooner rather than later  

You may face performance issues.  I do not know of any alternate method.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thank you for the responses.  The output gives me exactly what I want, but the performance is way to slow to use for end users.

amitchandak
Super User
Super User

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Difinity Conference

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.