Calculating Monthly Revenue per group per product per schedule

Wlknsn · March 6, 2020, 12:29pm

Hi,

I’m looking for a solution to calculate the monthly revenue of products (onetime) and services (monthly recurring). The Data model is the following:

Proposal
Bundle	Product	PriceType	Price/Month	Cost
X	A	Recurring	100	50
X	B	Recurring	150	70
X	C	Recurring	125	40
X	D	OneTime	3500	1200
Y	A	Recurring	100	40
Y	D	OneTime	3500	1200
Z	B	Recurring	150	70
Z	C	Recurring	125	40
Z	D	OneTime	3500	1200

Billing
Bundle	PriceType	Start	End	Months (Pro Rata)	Discount	sep/19	okt/19	nov/19	dec/19	jan/20	feb/20	mrt/20
X	Recurring	1/09/2019	31/10/2019	2,00	80%	75	75
X	Recurring	1/11/2019	2/02/2020	3,03	30%			262,5	262,5	262,5	7,875
X	Recurring	3/02/2020	6/03/2020	1,10	5%						356,25	35,625
X	OneTime	1/10/2019			50%		1750
Y	Recurring	1/02/2020	29/02/2020	1,00	10%						90
Y	OneTime	1/01/2020			0%					3500
Z	Recurring	1/09/2019	31/01/2020	5	0%	275	275	275	275	275
Z	OneTime	1/03/2020			20%							2800

Total Revenue / Month						350	2100	537,5	537,5	4037,5	454,125	2835,625


					Product	sep/19	okt/19	nov/19	dec/19	jan/20	feb/20	mrt/20
					A	20	20				90
					B	30
					C	25
					D		1750			3500		2800

As you can see, per bundle you may have multiple products which could have a specific pricetype to be applied to define the behavior of the pricing. Recurring products are combined and can then be split up in various periods (see the billing table), also here a discount is applied for each of those periods. All this is existing model.

The measure I’m looking for has the ability to show the actual revenue per month (pro rata) for each of those billing lines, or also from a product dimension perspective. (so sum of products per pricetype per bundle -> Per month * discount)

Eventually I will be looking for also the cumulative total (but should be easy from the monthly revenue).

Wlknsn · March 6, 2020, 12:43pm

Just FYI: ignore the cost column & also not all the values have been added in the product dimensions column at the bottom. This was more to depict what I’m looking for in multiple dimensions.

Wlknsn · March 7, 2020, 9:37am

Looks very similar to another topic calculating days between two dates (in this case it should be months + pro rated to come up with the dynamic duratio ). I guess this is the trickiest part in the above: Working with two dates - show amounts between two selected dates - #4 by sam.mckay

Melissa · March 7, 2020, 11:47am

Hi @Wlknsn,

Welcome to the forum!

So what you’re looking for seems pretty basic. To illustrate I’ve unpivoted your Billing table.

and added a Date table to the model which I’ve also marked as a Date table. Revenue per month:

Revenue per month = 
CALCULATE( SUM( tBilling[Invoiced] ),
    USERELATIONSHIP( 'Date'[MonthInCalendar], tBilling[Month in Calendar] )
)

Measure for cumulative revenu:

Cumulative revenue = 
VAR LastDay = MAX( 'Date'[Date] )
VAR Cumulative =
    CALCULATE( [Revenue per month],
        'Date'[Date] <= LastDay,
        ALL( 'Date' ) 
    )
VAR Result = IF( [Revenue per month] = BLANK(), BLANK(), Cumulative )

RETURN Result

.
With this result:

To achieve this you would need to do some more modelling because as it stands there doesn’t seem to be enough information to calculate it. But I trust this will get you started.

For more on Modelling and the Query Editor, please start here:

Next I would recommend:

.
And here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

Use the forum search to discover if your query has been asked before by another member.
When posting a topic with formula make sure that it is correctly formatted to preformated text </>.
Use the proper category that best describes your topic
Provide as much context to a question as possible.
Include demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

You can check the forum guidelines here How To Use The Enterprise DNA Support Forum. Not adhering to it may sometimes cause delay in getting an answer.

.
I hope this was helpful. Here are the sample files, note that when you update the FileLocation parameter all the queries will be restored.
eDNA - Calculating Monthly Revenue per group per product per schedule.pbix (118.7 KB)
Calculating Monthly Revenue per group per product per schedule.xlsx (13.3 KB)

Wlknsn · March 7, 2020, 12:55pm

Thanks for you reply, the point is that I only have price/month and not invoiced. Invoiced is what I would like to get per month based on start/end & price/month. So there is nothing to unpivot just yet.

Wlknsn · March 7, 2020, 8:11pm

So I wanted to elaborate a bit more, because it may not be as complex as I thought. Actually there are three things that needs a solution. But let’s start with the model (pbix added). Sample Monthly Revenue.pbix (135.8 KB)

You will notice that within a quote, products are bundled. Some products/services have onetime pricing some recurring (monthly). Each of these have a price.
All of these product prices are summed up per bundle price type in the billing schedule, after which the periods of the recurring items can be split up. In the example, you will see Bundle X split into 3 different periods for the recurring pricing with each different discounts for each period.

The aim is calculate the revenue up to monthly granularity. The Revenue is calculated by the Price * discount * duration (End date - Start date expressed in months). Price in billing schedule is always automatically a sum of each product with a pricetype within a bundle (so sum of all onetimes, some of all recurring).

I believe calculating the duration is the first question:

Of course the duration is simply EndDate - StartDate converted in months, but
a) we would need make use of inactive relationships to calendar table so that dates can be plotted onto a monthly table and automatically rolled up if for example years are selected. I assume it would be using some kind of ALLSELECTED to see which monthly price goes into which month.
b) how can I easily get duration of partial months? - Is there any easy way for example if 10 sept is selected, then that should result in Divide (if enddate, divide = 10 ; totaldays of month september, if startdate then , divide (totaldays of september - 10, totaldays of september)?
How to deal effectively with onetime/recurring pricing?
How can I show the monthly revenue also per product
=> Sum of Product Price * discount applied to each period for each product.

Given 1 & 3, I believe there should be a dynamic net value calculated & a dynamic duration based on the calendar to calculate the value per month.

Hope this makes more sense.

Melissa · March 9, 2020, 9:22pm

Hi @Wlknsn,

At this time I haven’t found an approach to calculate monthly revenue by product…

But have calculated Revenue in a monthly and yearly granularity, so let’s focus on that. I’ve made the following changes to the BillingSchedule table.

Added an EndDate2 column where the EndDate is equal to StartDate for OneTime records.
Added a Calculated column with a BundleNetPricePerDay

Next I created a Measure to calculate the Revenue per Day (actually by month in year) which due to the inactive relationships and the difference in calcs for on OneTime and partial Months looks like this:

Revenue per Day = 
VAR DaysInPeriod = 
    CALCULATE( COUNTROWS( 'Date' ),
        FILTER( 'Date',
            'Date'[Date] >= MIN( BillingSchedule[StartDate] ) &&
            'Date'[Date] <= MAX( BillingSchedule[EndDate2] )
        )
    )
VAR DaysInMonth = IF( HASONEVALUE( 'Date'[YearMonthnumber] ), VALUES( 'Date'[DaysInMonth] ), BLANK() )
RETURN

IF( DaysInPeriod = DaysInMonth, 
    SUMX( 
        CALCULATETABLE( BillingSchedule, 
            FILTER( VALUES( BillingSchedule[StartDate] ), BillingSchedule[StartDate] <= MAX( 'Date'[Date] )),
            FILTER( VALUES( BillingSchedule[EndDate2] ), BillingSchedule[EndDate2] >= MIN( 'Date'[Date] ))
        ),
        BillingSchedule[BundleTotalNetPricePerPriceType] ),
    IF( SELECTEDVALUE( BillingSchedule[PriceType] ) <> "OneTime" && ISFILTERED( 'Date'[YearMonthnumber] ),
    SUMX( 
        CALCULATETABLE( BillingSchedule,
        FILTER( VALUES( BillingSchedule[StartDate] ), BillingSchedule[StartDate] <= MAX( 'Date'[Date] )),
        FILTER( VALUES( BillingSchedule[EndDate2] ), BillingSchedule[EndDate2] >= MIN( 'Date'[Date] ))),
        BillingSchedule[BundleNetPricePerDay] * DaysInPeriod
    ),
    SUMX( 
        CALCULATETABLE( BillingSchedule,
        FILTER( VALUES( BillingSchedule[StartDate] ), BillingSchedule[StartDate] <= MAX( 'Date'[Date] )),
        FILTER( VALUES( BillingSchedule[EndDate2] ), BillingSchedule[EndDate2] >= MIN( 'Date'[Date] ))),
        BillingSchedule[BundleTotalNetPricePerPriceType] * COUNTROWS( VALUES( 'Date'[YearMonthnumber] )) 
    )
)) +
IF( SELECTEDVALUE( BillingSchedule[PriceType] ) = "OneTime",
    SUMX( 
        CALCULATETABLE( BillingSchedule,
            FILTER( VALUES( BillingSchedule[StartDate] ), BillingSchedule[StartDate] <= MAX( 'Date'[Date] )),
            FILTER( VALUES( BillingSchedule[StartDate] ), BillingSchedule[StartDate] > MIN( 'Date'[Date] ))
        ),
        BillingSchedule[BundleTotalNetPricePerPriceType] ),
    BLANK()
)

.
Finally I created the Revenue measure to sum it all up independent of Bundle, PriceType and Month- or Yearly granularity.

Revenue = 
SUMX(
    FILTER (
    SUMMARIZE (
        CROSSJOIN (
            VALUES ( 'Date'[YearMonthnumber] ),
            VALUES ( BillingSchedule[Bundle] ),
            VALUES( BillingSchedule[PriceType] )
        ),
        'Date'[YearMonthnumber],
        BillingSchedule[Bundle],
        BillingSchedule[PriceType],
        "Revenue" | [Revenue per Day]
    ),
    NOT ( ISBLANK ( [Revenue] ) )
    ), [Revenue]
)

.

Here’s your file. I hope this is helpful.
eDNA - Sample Monthly Revenue.pbix (147.7 KB)

Wlknsn · March 9, 2020, 10:18pm

Hi Melissa, this is absolutely amazing! I’m very much impressed.

So for the missing part, perhaps it helps to understand that the BundleTotalPriceType is currently a fixed number, but in reality it’s like a summarize of Bundles, Bundles[Bundle],Bundles[PriceType], sum([Price/Month]). This value is then used for each schedule line per pricetype & per bundle. (or perhaps an allexcept on bundle & pricetype of the related products in Bundle).

EnterpriseDNA · March 9, 2020, 10:33pm

Hi @Wlknsn, please don’t forget if your question has been answered within the forum it is important to mark your thread as ‘solved’. Thanks!

Wlknsn · March 9, 2020, 10:35pm

Hi,

I’m kinda hoping for a solution to get the product dimension into the mix. This would definitely close this as a solution. Fingers crossed here.

Regards

Wlknsn · March 10, 2020, 1:10pm

@Melissa I found the last piece (not sure how to do it but …)

Your calculation is 100% correct but this needs to be done on top of the following table.

Bundle	PriceType	StartDate	Quote[Product]Join	Quote[PriceMonth]	Discount	BundleTotalPricePerPriceType	BundleTotalNetPricePerPriceType	DurationProRata	BundleNetPricePerDay	EndDate2
X	Recurring	01/01/2019	A	40	1	90	0	3,00	0	43555
X	Recurring	01/01/2019	B	30	1
X	Recurring	01/01/2019	C	20	1
X	Recurring	01/04/2019	A	40	0,3	90	63	5,00	2,058823529	43708
X	Recurring	01/04/2019	B	30	0,3
X	Recurring	01/04/2019	C	20	0,3
X	Recurring	01/09/2019	A	40	0,05	90	85,5	5,00	2,794117647	43861
X	Recurring	01/09/2019	B	30	0,05
X	Recurring	01/09/2019	C	20	0,05
X	OneTime	01/01/2019	D	200	0,1	200	180	0,00	180	43466
Y	Recurring	01/03/2019	A	40	0	40	40	22,00	1,30952381	44196
Y	Recurring	01/01/2021	A	40	0	40	40	12,00	1,315068493	44561
Y	OneTime	01/01/2020	D	200	0	200	200	0,03	200	43831
Z	Recurring	15/02/2020	C	20	0,2	20	16	4,90	0,653061224	44022
Z	OneTime	01/01/2021	D	200	0	200	200	0,00	200	44197

So first a join needs to be made between Quote & Quote Schedule where for each schedule line and bundle, the related products are repeated with their price. From this the discount can be taken (which was set at the schedule line). That resulting price for each of those joined products combined with your revenue calculation should do the trick 100%.

Could you/someone let me know how this join could be made?

Melissa · March 10, 2020, 1:41pm

That looks helpful indeed.

Go to Power Query

add an Index from 1 to the Quote query
select the BillingSchedule query Merge that with the Quote query using the Bundle and PriceType as a key
Expand both Product and Index column
Where the Index <>1 replace the values with null for the columns you left blank

Wlknsn · March 10, 2020, 4:40pm

Marked this as solution, as it works, but I’m trying to do this in DAX to keep it in memory as I’m afraid massive amounts of data is going to be generated at refresh time, and in the current setup, the refresh will happen on demand so … going with Generate & Select Columns.

MANY THANKS for this!

Melissa · March 10, 2020, 5:09pm

Glad I could help.

sam.mckay · March 10, 2020, 10:26pm

Incredible solution Melissa! Nice work

Wlknsn · March 10, 2020, 10:39pm

Yes indeed … made only a small change to the Onetime time intelligence from:

IF( SELECTEDVALUE( BillingSchedule[PriceType] ) = “OneTime”,
SUMX(
CALCULATETABLE( BillingSchedule,
FILTER( VALUES( BillingSchedule[StartDate] ), BillingSchedule[StartDate] <= MAX( ‘Date’[Date] )),
FILTER( VALUES( BillingSchedule[StartDate] ), BillingSchedule[StartDate] > MIN( ‘Date’[Date] ))
),
BillingSchedule[BundleTotalNetPricePerPriceType] ),
BLANK()
)

to

IF( SELECTEDVALUE( BillingSchedule[PriceType] ) = “OneTime”,
SUMX(
CALCULATETABLE( BillingSchedule,
FILTER( VALUES( BillingSchedule[StartDate] ), BillingSchedule[StartDate] = MAX( ‘Date’[Date] ))
** ),**
BillingSchedule[BundleTotalNetPricePerPriceType] ),
BLANK()
)