cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 283 members 2,858 guests
Please welcome our newest community members: