Create a line chart with Date on X-axis that are "active" between dates

[CornerACK]

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!

[v-piga-msft · Accepted 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

[lfrancfort]

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,