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
dickvankl
Frequent Visitor

Make line graph with starting date and end date

Hello,

 

I have a dataset with start date and end date per subscription. Next to that I have a column with the type of subscription. I want to make a line chart with the number of subscriptions over the months. Does anayone know how to do this?

 

The data looks as follows:

Sample.JPG

When the cell in the End date column is empty the subscription is ongoing subscription. So when the date is between the starting date and the end date or the date is after the starting date and the end date is empty the customer has a subscription. 

 

I want to make a line chart with at the x-axis the date (for example first of each month) and at the y-axis the number of subscriptions at that date.

Next to that I want to use a filter with the subscription type, so I can also see the linechart with number of subscriptions over time for a certain subscription type. 

1 ACCEPTED SOLUTION
jdbuchanan71
Super User
Super User

Hello @dickvankl 

You will need a date table in your model for my measure to work.  You can make a simple one with the following DAX code

Dates = 
VAR DateRange = CALENDARAUTO()

RETURN 
ADDCOLUMNS(
    DateRange,
    "Year",YEAR([Date]),
    "Month",FORMAT([Date],"mmmm"),
    "Year Month", FORMAT([Date],"yyyy-mmmm"),
    "YearMonthSort",YEAR([Date])*100 + MONTH([Date]),
    "ShortName",FORMAT([Date],"ddd"),
    "IsWeekDay", NOT WEEKDAY( [Date] ) IN {1,7}
)

Then a measure like so to count the subsriptions.

Subscription Count = 
VAR _FirstDate = FIRSTDATE ( Dates[Date] )
VAR _LastDate = LASTDATE ( Dates[Date] )

RETURN
CALCULATE (
    DISTINCTCOUNT ( Subscriptions[Subscription number] ),
    Subscriptions[Starting date] <= _LastDate,
    Subscriptions[End date] > _FirstDate || ISBLANK ( Subscriptions[End date] )
)

You pull the Year Month field into the axis and the measure into the values:

Subscriptions.jpg

View solution in original post

9 REPLIES 9
matan_benyishay
Helper II
Helper II

And here is my code, because I couldn't paste it in one place:

Num of clients = 
CALCULATE(
DISTINCTCOUNT('Program enrollments'[Clients Unique Identifier]),
filter('Program enrollments', 
'Program enrollments'[Program Start Date]<=LASTDATE('Calendar Table'[Date].[Date]) && 
('Program enrollments'[Program Exit Date]>=FIRSTDATE('Calendar Table'[Date].[Date]) || ISBLANK('Program enrollments'[Enrollments Project Exit Date])))))

@matan_benyishay 

Remove the join to the date table.  The filtering of the dates is handled in the criteria of the measure.

Thanks. I tried that, and I get a flat line in that case.

matan_benyishay_0-1606927922370.png

 

Couple of quesitons,

Is your Calendar table marked as the date table in the model?

Is your date on the X axis of the chart from the Calendar table?

You don't need to use 'Calendar Table'[Date].[Date], the second [Date] is from the auto time intelligence that powerBI throws in when you don't have a date table.

You should only use 'Calendar Table'[Date] in your measure.

Thanks, that handled it! No, I had not set it as the date table in the model, that wasn't something I understood yet. 

matan_benyishay
Helper II
Helper II

Hi,

I'm trying something like this (pretty basic) but I think there might be something wrong with my join.

In one table, I have client unique ID, program name, program start and end date. I've made another table with calendar() and added things like fiscal year to it.

I tried to join program start date to the date in the calendar table. I then created the count variable as above. (I'm trying to post it without the code because it wasn't working with it.)

 
However, the count doesn't work right compared to checking it manually. That probably make sense since the join is weird.
Any pointers about how I should join the tables?
jdbuchanan71
Super User
Super User

Hello @dickvankl 

You will need a date table in your model for my measure to work.  You can make a simple one with the following DAX code

Dates = 
VAR DateRange = CALENDARAUTO()

RETURN 
ADDCOLUMNS(
    DateRange,
    "Year",YEAR([Date]),
    "Month",FORMAT([Date],"mmmm"),
    "Year Month", FORMAT([Date],"yyyy-mmmm"),
    "YearMonthSort",YEAR([Date])*100 + MONTH([Date]),
    "ShortName",FORMAT([Date],"ddd"),
    "IsWeekDay", NOT WEEKDAY( [Date] ) IN {1,7}
)

Then a measure like so to count the subsriptions.

Subscription Count = 
VAR _FirstDate = FIRSTDATE ( Dates[Date] )
VAR _LastDate = LASTDATE ( Dates[Date] )

RETURN
CALCULATE (
    DISTINCTCOUNT ( Subscriptions[Subscription number] ),
    Subscriptions[Starting date] <= _LastDate,
    Subscriptions[End date] > _FirstDate || ISBLANK ( Subscriptions[End date] )
)

You pull the Year Month field into the axis and the measure into the values:

Subscriptions.jpg

Hi  @jdbuchanan71 , hope you are fine!

 

Your solution helped me to solve a very similar case! Thank yo very much! 

 

But I also need to add some information to the same chart and maybe you can help me. 

 

I have an "Amount" column, where I put the money that each customer has to pay monthly for the service. 

 

Therefore, I aded a measure as follows (I don´t have blank posibility in "end date" so I omitted that): 

 

Subscription Amount =

VAR _FirstDate = FIRSTDATE ( Dates[Date])

VAR _LastDate = LASTDATE ( Dates[Date])

RETURN

CALCULATE (

SUM ( 'Subscription[Amount]);

Subscription[Starting Date] <= _LastDate;

'Subscription[End Date] > _FirstDate

)

 

This way, I pull to a "Column & Line Chart" "Year month" to shared axis, and "Subscripion Amount" measure to column values. 

And I get the SUM of all individual payments of my customers for each month included in the contract. 

Also dragged the Customer Name to column series. 

 

I now need to display the "Accumulated Income" I was payed over the timeline. 

Example...

- if January 2019 I had "customer A" that pas $100, and "Customer B" that pays $200. Then the accumulated for January 2019 is $300.

- if February 2019 I still have customers A and B, and I got a new contract with "Customer C" for $400. Then the accumulated income for February 2019 should be $1000 ($300 in January from A and B, and $700 in February for "A, B and C"). 

- if March 2019 I still have customers B and C, but contract for A is finished. Then the accumulated income for March 2019 should be $1600 ($1000 accumulated by February 2019, plus $200 for customer B and $400 for customer C).

 

Hope I was clear with the explanation and many thanks in advance!!!

Regards! 

Matias.

@jdbuchanan71thank you very much!

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.