Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Goodday everyone,
I have created a project with Client registrations. When they are added to the database they get a StartDate. From that moment they become "active". If a client doesnt want to be registered anymore, they will have a EndDate. From that moment they will no be longer "active"
This is my example Table:
ClientId | StartDate | EndDate | CityId | Age |
1 | 1-1-2018 | 5-5-2018 | 500 | 88 |
2 | 12-2-2018 | 13-6-2018 | 220 | 21 |
3 | 25-8-2018 | 11-12-2018 | 321 | 23 |
4 | 2-3-2018 | 11-11-2018 | 321 | 46 |
5 | 7-4-2018 | 9-12-2018 | 500 | 59 |
6 | 10-2-2018 | 19-3-2018 | 500 | 71 |
So, if I want to count all Clients that are active before the date of 01-09-2018. I created a filter with EndDate is before 01-09-2018 (and also StartDate is after 01-01-2018) >> See my PBIX fil (https://www.dropbox.com/s/fpw8ix6tpje8utj/Example%20StartEndDate%20PowerBI.pbix?dl=0).
But here comes the question! I want to create a line chart that showes me per month all the active clients.
Client 1 is active in the following months: january february march april may.
So I want the cliënt to be counted in the line chart for all those months.
Same for client 2 who needs to be shown in months: february march april may june
Etc.
So the following months will have these values with Active Clients:
january 1
february 3
march 4
april 4
may 4
june 3
july 2
august 3
september 3
october 3
november 3
december 2
Is there a way I can make a line chart visualisation in Power BI to make this happen?
Thanks in advance!
Solved! Go to Solution.
Hi @CornerACK ,
For your sceanrio, you could follow the steps below to get your desired.
1. Create a calendar table and then create a measure.
calendar = CALENDARAUTO() Measure = VAR a = MONTH ( MAX ( 'calendar'[Date] ) ) RETURN COUNTROWS ( FILTER ( 'FClient (2)', a >= MONTH ( 'FClient (2)'[StartDate] ) && a <= MONTH ( 'FClient (2)'[EndDate] ) ) )
Here is your output.
More details, please refer to my attachment.
Best Regards,
Cherry
Hi @CornerACK ,
For your sceanrio, you could follow the steps below to get your desired.
1. Create a calendar table and then create a measure.
calendar = CALENDARAUTO() Measure = VAR a = MONTH ( MAX ( 'calendar'[Date] ) ) RETURN COUNTROWS ( FILTER ( 'FClient (2)', a >= MONTH ( 'FClient (2)'[StartDate] ) && a <= MONTH ( 'FClient (2)'[EndDate] ) ) )
Here is your output.
More details, please refer to my attachment.
Best Regards,
Cherry
Hello,
Is there a way of using this scenario for multiple products ?
I've got the following tables :
- Product Table, with ProductID, Product Name and other product infos
- Customers Table, with CustomerID, Customer Name and other customers infos (phone, address, email...)
- Product-Customers Table, with ProductID, CustomerID, startdate, enddate.
I would like to get for each months, for each product, the number of customers during this month (or at the end of the month).
I think I have to use a matrix, but I can't find which DAX to create for this goal.
Thank you,
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |