cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

Accepted Solutions
Highlighted
Resident Rockstar
Resident Rockstar

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

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
Highlighted
Resident Rockstar
Resident Rockstar

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

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

Highlighted
Frequent Visitor

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

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
Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors