Forum Discussion
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:
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.
Hello Anonymous
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:
9 Replies
- jdbuchanan71
Super User
Hello Anonymous
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:
jdbuchanan71thank you very much!
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.
matan_benyishayHelper 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?- matan_benyishay
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])))))- jdbuchanan71
Remove the join to the date table. The filtering of the dates is handled in the criteria of the measure.
- matan_benyishay
Thanks. I tried that, and I get a flat line in that case.
