cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
CornerACK
Frequent Visitor

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

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:

 

ClientIdStartDateEndDateCityIdAge
11-1-20185-5-201850088
212-2-201813-6-201822021
325-8-201811-12-201832123
42-3-201811-11-201832146
57-4-20189-12-201850059
610-2-201819-3-201850071

 

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!

 

 

1 ACCEPTED SOLUTION
v-piga-msft
Resident Rockstar
Resident Rockstar

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.

Capture.PNG

More details, please refer to my attachment.

Best  Regards,

Cherry

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-piga-msft
Resident Rockstar
Resident Rockstar

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.

Capture.PNG

More details, please refer to my attachment.

Best  Regards,

Cherry

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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,

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors