Calculating Monthly Revenue per group per product per schedule

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).

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.

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

Hi @Wlknsn,

Welcome to the forum!

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

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:
image

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)

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.

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:

  1. 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)?

  2. How to deal effectively with onetime/recurring pricing?

  3. 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.

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.

  1. Added an EndDate2 column where the EndDate is equal to StartDate for OneTime records.
  2. 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)

2 Likes

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).

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!

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

@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?

That looks helpful indeed.

Go to Power Query

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

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!

:+1:

Glad I could help.

Incredible solution Melissa! Nice work

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()
)

1 Like