How do i create a barchart with active rows?

[StanTheMan]

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

[v-piga-msft]

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

[Greg_Deckler · Accepted 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