cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kattlees
Post Patron
Post Patron

Counting rows on a certain date

I need to get a count of the number of rows on a given day in a given month. My issue is when their arrival date or departure date is in a different month.

 

Example:

Visitor                 arrival                     departure

123456               6/30/21                    7/2/21

123457               7/1/21                      7/3/21

123458                7/2/21                   8/3/21

 

I need this result:

7/1/21 - 2

7/2/21 - 2

7/3/21 - 1

 

I have two tables:

Calendar with Date field

Then Guests with guest number, arrival date, departure date.

 

If I do a relationship between Calendar Date and arrival date - it doesn't count the peson who arrived the month prior.

If I do a relationship between Calendar Date and departure date - it doesn't count the person who leaves the next month.

 

What step am I missing?

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

@kattlees 
Take a look at these two Quick Measures as I think you want something like them.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

View solution in original post

2 REPLIES 2
ryan_mayu
Super User
Super User

@kattlees 

maybe you can try this

Measure = calculate(COUNTROWS('Table'),FILTER('Table','Table'[arrival]<=max('Date'[Date])&&'Table'[departure]>max('Date'[Date])))

pls see the attachment below





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

Proud to be a Super User!




Greg_Deckler
Super User
Super User

@kattlees 
Take a look at these two Quick Measures as I think you want something like them.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.