# By- Month Subscriptio Revenue using only Subscription Start and End Date (or "N/A" as End Date)

Hi,
I am trying to calculate the Subscription Revenue by Month but I only have Start Date, End Date.
If the subscription is ongoing, the End Date = “N/A”.

My goal is to show, December Subscription Revenue = \$x, January Subscription Revenue = \$y, etc.

I am using the date table from Enterprise DNA’s class to get the Months and the attached file as my data.

I’d appreciate any help you can offer. I tried using a filter table formula, but adding it here will just be more confusing.

Subscription - Anonymous Data.xlsx (467.5 KB)

1. in calculating revenue, the first month should be assigned “initial price” and then every subsequent month until cancellation gets assigned “recurring price”, correct?
2. if someone signs up or cancels midmonth, do they still get charged the full month’s price or pro-rated amount?

Thanks.

• Brian

Thank you for helping with this.
Regarding #1, yes.
Regarding #2, yes.

Thank you,
Scott

#2 wasn’t yes or no.

I’m assuming you meant full amount, correct?

Thanks.

• Brian

Oops. Sorry about that. There’s no pro-rata for a month, it’s full recurring revenue no matter what day you sign up but the recurring doesn’t kick in until after your first month.
So month 1 Revenue = Initial Price
Subsequent Months = Recurring Revenue price per month, no pro-rata.

Thank you,
Scott

This is a really interesting problem. If you take the data as is, it’s incredibly difficult to get the result you want. However, if you reshape the data in Power Query, it’s two short measures to get right to the desired result:

`Total Billed Revenue = SUM( 'Subscriptions Transformed'[Billed Revenue] )`

and

``````Billed Revenue by Month TREATAS =

CALCULATE(
[Total Billed Revenue],
TREATAS(
VALUES( Dates[MonthnYear] ),
'Subscriptions Transformed'[Billed MoInYr]
)
)
``````

Drop the second measure into a table or matrix with the month on rows and you get this:

I used TREATAS here for two reasons:

1. The transformed fact table data I created had no relationship in the model to the date table (though I could have linked them together via the date columns)
2. More importantly, the relationship between the tables based on MonthInYear is a many-to-many relationship that is handled perfectly by TREATAS.

The heavy lift here is done by Power Query. Here’s an outline of the general strategy I used to transform the data:

1. where End Subscription Date was blank (N/A), I used today’s date as the end month for revenue collection (this solution is dynamic, and will recalculate every time with the new end date until the subscriber cancels)

2. created the MonthInYear numeric format to correspond to the start and end subscription dates (e.g. 20200400 for April 2020).

3. for each subscriber, I created a list of MonthInYear numbers in sequence incremented by 100 to represent one observation for each month that they were subscriber between the start and end dates, and then expanded that list by values with a space delimiter between them

4. then I unpivoted the list created in 3) to create a separate record for each subscriber and each month of subscription.

5. then created a billed revenue column, where if the MonthInYear value in the unpivoted column equaled the start subscription MonthInYear value, the column utilized the initial price, otherwise it used the recurring price.

Here’s what the transformed table looks like:

From there you just apply the DAX above, and I think you should be good to go.

Here’s the M code for the transformation, with full solution file posted below.

``````let
Subscriptions_Sheet = Source{[Item="Subscriptions",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Subscription ID", Int64.Type}, {"Subscription Status", type text}, {"Initial Price", type number}, {"Recurring Price", type number}, {"Subscription Created At", type date}, {"Subscription Start Date", type date}, {"Subscription End Date", type any}, {"Next Rebilling Date", type date}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type","N/A",null,Replacer.ReplaceValue,{"Subscription End Date"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"Subscription End Date", type date}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Changed Type1", "Subscription End Date", "Subscription End Date - Copy"),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom",{{"Today", type date}}),
#"Sorted Rows" = Table.Sort(#"Changed Type2",{{"Subscription End Date - Copy", Order.Descending}}),
#"Added Conditional Column" = Table.AddColumn(#"Sorted Rows", "EndRevDate", each if [Subscription End Date] = null then [Today] else [Subscription End Date]),
#"Removed Other Columns" = Table.SelectColumns(#"Added Conditional Column",{"Subscription ID", "Initial Price", "Recurring Price", "Subscription Start Date", "EndRevDate"}),
#"Added Custom Column" = Table.AddColumn(#"Removed Other Columns", "Start MoInYr", each Text.Combine({Date.ToText([Subscription Start Date], "yyyy"), Date.ToText([Subscription Start Date], "MM"), "00"}), type text),
#"Changed Type3" = Table.TransformColumnTypes(#"Added Custom Column",{{"Start MoInYr", Int64.Type}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type3", "End MoInYr", each Text.Combine({Date.ToText([EndRevDate], "yyyy"), Date.ToText([EndRevDate], "MM"), "00"})),
#"Changed Type4" = Table.TransformColumnTypes(#"Added Custom1",{{"End MoInYr", Int64.Type}}),
#"Added Custom3" = Table.AddColumn(#"Changed Type4", "Number of Months", each ([End MoInYr] - [Start MoInYr]+100)/100),
#"Renamed Columns" = Table.RenameColumns(#"Added Custom3",{{"Number of Months", "Revenue Months"}}),
#"Added Custom2" = Table.AddColumn(#"Renamed Columns", "ListMoInYr", each List.Numbers( [Start MoInYr], [Revenue Months], 100 )),
#"Extracted Values" = Table.TransformColumns(#"Added Custom2", {"ListMoInYr", each Text.Combine(List.Transform(_, Text.From), " "), type text}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "ListMoInYr", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"ListMoInYr.1", "ListMoInYr.2", "ListMoInYr.3", "ListMoInYr.4"}),
#"Changed Type5" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"ListMoInYr.1", Int64.Type}, {"ListMoInYr.2", Int64.Type}, {"ListMoInYr.3", Int64.Type}, {"ListMoInYr.4", Int64.Type}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type5", {"Subscription ID", "Initial Price", "Recurring Price", "Subscription Start Date", "EndRevDate", "Start MoInYr", "End MoInYr", "Revenue Months"}, "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
#"Renamed Columns1" = Table.RenameColumns(#"Removed Columns",{{"Value", "Billed MoInYr"}}),
#"Added Conditional Column1" = Table.AddColumn(#"Renamed Columns1", "Billed Revenue", each if [Billed MoInYr] = [Start MoInYr] then [Initial Price] else [Recurring Price]),
#"Changed Type6" = Table.TransformColumnTypes(#"Added Conditional Column1",{{"Billed Revenue", Int64.Type}})
in
#"Changed Type6"
``````

I hope this is helpful. Definitely an interesting problem…

2 Likes

Hi @BrianJ,

Amazing stuff. Really like your approach on this!

Thanks! I was inspired by your “learn M by Googling” advice the other day. But I learned that when you do find the correct function, you need to read it carefully. For this one, when I found list.numbers, I thought “great - works like DATESBETWEEN”. Ummm…no it doesn’t. Couldn’t figure out why it was taking so long to run, until I realized the second parameter I was using (in the form of MonthInYear) was requesting it to create over 20 million numbers per record.

• Brian
1 Like

That’s impressive. Thank you @BrianJ . I need to take some time and try to understand it I learn several new things every time I post here.

Thanks. As you work through this, please feel free to give a shout with any questions you might have. While the DAX is quite straightforward if you have a good understanding of TREATAS, there were some wrinkles in the Power Query portion that might need some additional explanation as you work through it.

I learn new stuff on this forum every single day. And actually learned a lot working through this solution.

• Brian

In thinking more about this one, one thing I wanted to pass along was a recent conversation I had with @dsiffredi on a similar type of problem where the initial data transformation made all the difference in the ultimate solution. I find the best place to start on challenging problems like this one is by considering “what would be the ideal organization/structure of this data to answer the question being asked?” . Like Daniel’s problem below, we started with a wide and short structure (lots of columns, relatively few rows), and ended up with a narrow and long structure (fewer columns, more records), which will often be the right direction in which to head.

Anyway, just some food for thought as you work through the solution…

• Brian