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

Accepted Solutions
Super User
Super User

Re: Make line graph with starting date and end date

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

2 REPLIES 2
Super User
Super User

Re: Make line graph with starting date and end date

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

dickvankl Frequent Visitor
Frequent Visitor

Re: Make line graph with starting date and end date

@jdbuchanan71thank you very much!

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (1,039)