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

Calculate the count of customers between two dates.

I have two date columns, StartDate, and Enddate. What I wanted to do is whenever I select a date from the filter, I want to get the calculated value of the count of customers where StartDate < selected date > EndDate.

How can I calculate the count of customers between two dates?
Do I have to create a calendar table to use the date as a filter?
If yes, Power BI allows only one active relationship, so how can I connect the date table with the two date columns StartDate and EndDate with the fact table?

 

Regards

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User IV
Super User IV

@Samrawit21 , Refer if my blog on similar topic can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Highlighted

To count distinct values, I used the expression below, and it works fine.
 
Current = CALCULATE([Count of customers],(FILTER(Table,Table[StartDate] < max('Date'[Date]) && (Table[EndDate] > max('Date'[Date])))))

View solution in original post

3 REPLIES 3
Highlighted
Super User IV
Super User IV

@Samrawit21 , Refer if my blog on similar topic can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Highlighted

@amitchandak 

Thank you for your reply. can I use Countx and Distinct on the expression?
I want to count distinct customers between two dates.

Highlighted

To count distinct values, I used the expression below, and it works fine.
 
Current = CALCULATE([Count of customers],(FILTER(Table,Table[StartDate] < max('Date'[Date]) && (Table[EndDate] > max('Date'[Date])))))

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors