Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors