Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi guys,
I'm struggling with creating a barchart visualization in which active rows per month are shown. It seems to me like this is such a simple question but i can't seem to get my head arround it.
I have a dataset that looks like this. (can't share the real one because of privacy)
Ordernumber | Clientnumber | Start Date | End Date |
1 | 1 | 31-12-2018 | |
2 | 1 | 1-12-2018 | 31-12-2018 |
23 | 2 | 8-1-2019 | 8-2-2019 |
3 | 3 | 1-1-2019 | 28-2-2019 |
14 | 3 | 1-1-2019 |
Getting the correct numbers for a single month or period could be achieved with using a filter or some dax:
Active in Jan 2019 = IF([Start_date].[Date] <= 31-1-2019 && [End_date].[Date] >= 1-1-2019 || [Start_date].[Date] <= 31-1-2019 && ISBLANK([End_date].[date]); 1; null)
I want to create a barchart for the whole year that visualises how many clients or orders were active per month.
So if I use the dataset from above it would look something like this.
Active clients:
3 3 2
Jan 2019, Feb 2019, Mar 2019, Etc.
Active orders:
4 4 2
Jan 2019, Feb 2019, Mar 2019, Etc.
I hope I have described the problem well enough and am curious if someone has a clever solution for this.
Thanks in advance,
Stan
Solved! Go to Solution.
That's actually a decpetively tricky problem, but a solved one. 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
Hi @StanTheMan,
What about modifing your fomula like below? Table is the another calendar table.
Column = VAR start_date = STARTOFMONTH ( 'Table'[Date] ) VAR end_date = ENDOFMONTH ( 'Table'[Date] ) RETURN IF ( [Start Date].[Date] <= end_date && 'Table1'[End Date].[Date] >= start_date || 'Table1'[Start Date].[Date] <= end_date && ISBLANK ( 'Table1'[End Date].[Date] ), 1, BLANK () )
Then you could count the column=1 for each month.
Best Regards,
Cherry
That's actually a decpetively tricky problem, but a solved one. 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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
97 | |
82 | |
75 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |