Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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