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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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