Reporting on targets with recurring billing cycles

[gjhannam23]

Hi all,

I have a client that has multiple product streams billed in a variety of cycles [RecurringBillingCycle], which can be Annual, Quarterly, Monthly, with an associated profit amount for each item [UnitProfit] which is posted as £X for the cycle i.e. not a total contract value. These streams have a start and end date, [RecurringStartDate] and [RecurringEndDate] respectively. 

Here is a sample data set:

DocType	RecurringBillingCycle	RecurringStartDate	RecurringEndDate	UnitProfit	ProfitAmount
INVOICE	Annual	30/10/2019 00:00	17/10/2020 00:00	£5	97005.51
INVOICE	Annual	20/12/2019 00:00	20/12/2020 00:00	£441	97005.51
INVOICE	Annual	21/01/2020 00:00	21/01/2021 00:00	£4	97005.51
INVOICE	Monthly	01/09/2016 00:00	31/08/2019 00:00	£3	97005.51
INVOICE	Monthly	01/11/2019 00:00	31/10/2020 00:00	£26	97005.51
INVOICE	Monthly	01/12/2019 00:00	01/01/2020 00:00	£1,493	97005.51
INVOICE	Quarterly	01/09/2019 00:00	31/08/2021 00:00	£1,838	97005.51
INVOICE	Quarterly	01/10/2019 00:00	01/10/2020 00:00	£60	97005.51

I would like to report monthly starting positions versus a predetermined monthly target given the fact that the recurring invoices will initalise each month with £X already accounted for. 

The bit I am having trouble with is the periodic billing. If there are any suggestions on how to break this down further it would be much appreciated.

I have attempted to use OPENINGBALANCEMONTH but the below returns no value.

OpeningMonthlyBalance = OPENINGBALANCEMONTH(SUM(DocumentItems[UnitProfit]),DocumentItems[RecurringStartDate])

Let me know if there is any extra information you require.

Thanks, Greg

Here is the data model in case it is of help. [UnitProfit] sits in DocItems, [ProfitAmount] in DocHeaders

[zoloturu]

Hi @gjhannam23 ,

Did you tried next DAX functions: OPENINGBALANCEMONTH (QUARTER or YEAR), CLOSINGBALANCEMONTH (QUARTER or YEAR)?

If this not helps then provide sample data and calculations, for instance, in Excel.

Regards,
Ruslan Zolotukhin (zoloturu)
BI Engineer at Akvelon Inc. / Kharkiv Power BI User Group Leader / DAX & Power BI Trainer
-------------------------------------------------------------------
Did I answer your question? Mark my post as a solution!
It was useful? Press Thumbs Up!

You are from Ukraine? If yes then welcome to Power BI User Group - KhPUG website. Other country? Check and find proper one - Power BI User Groups

[gjhannam23]

Updated original post based on feedback

[zoloturu]

@gjhannam23 ,

Thanks for the updates. Can you include a column with expected (desired) data which you need? And describe the formula logic should be used (not relate to DAX but in general). It is not fully clear for the moment. 

Thanks

Regards,
Ruslan Zolotukhin (zoloturu)
BI Engineer at Akvelon Inc. / Kharkiv Power BI User Group Leader / DAX & Power BI Trainer

[gjhannam23]

Hi Ruslan,

I would have thought the logic for the measure should be something like:

MonthlyRecurringProfit = 
IF( RecurringBillingCycle = "Annual") {
  MonthlyRecurringProfit = UnitProfit/12
} else if (RecurringBillingCycle = "Quaterly") {
  MonthlyRecurringProfit = UnitProfit/3
} else {
  MonthlyRecurringProfit = UnitProfit
}

The expected result would be a value per month of the amount of recurring revenue.  But this wouldn't take into account start/end dates

I would also like to see this for future months ideally, which the above would not solve I believe.

Thanks

[zoloturu]

@gjhannam23 ,

Try this one:

MonthlyRecurringProfit =
VAR __BillingCycle =
    SELECTEDVALUE ( [RecurringBillingCycle] )
VAR __Profit =
    SUM ( [UnitProfit] )
RETURN
    SWITCH (
        __RecurringBillingCycle,
        "Annual", __Profit / 12,
        "Quaterly", __Profit / 3,
        __Profit
    )

Regards,
Ruslan Zolotukhin (zoloturu)
BI Engineer at Akvelon Inc. / Kharkiv Power BI User Group Leader / DAX & Power BI Trainer
-------------------------------------------------------------------
Did I answer your question? Mark my post as a solution!
It was useful? Press Thumbs Up!

You are from Ukraine? If yes then welcome to Power BI User Group - KhPUG website. Other country? Check and find proper one - Power BI User Groups

[gjhannam23]

That formula has worked in as much as it splits the billing down. However it doesn't take into account the time periods we are dealing with. I think I need to rework @Greg_Deckler 's periodic billing example to include multiple billing cycles. Here is my attempt below:

Monthly Recurring Amount = 
VAR tmpCalendar = ADDCOLUMNS(HeaderDocDate,"Month",MONTH([Date]),"MonthYear",VALUE(YEAR([Date]) & FORMAT(MONTH([Date]),"0#")))
VAR tmpBilling = ADDCOLUMNS(DocumentItems,"MonthYearBegin",VALUE(YEAR([RecurringStartDate]) & FORMAT(MONTH([RecurringStartDate]),"0#")),
                                      "MonthYearEnd",VALUE(YEAR([RecurringEndDate]) & FORMAT(MONTH([RecurringEndDate]),"0#")))
VAR tmpTable = 
SELECTCOLUMNS(
    FILTER(
        GENERATE(
            tmpBilling,
            SUMMARIZE(tmpCalendar,[Year],[Month],[MonthYear])
        ),
        [MonthYear] >= [MonthYearBegin] &&
        [MonthYear] <= [MonthYearEnd]
    ),
    "Customer",'DocumentItems'[DocID],
    "Year",[Year],
    "Month",[Month],
    "Amount",[MonthlyRecurringProfit]
)
RETURN SUMX(tmpTable,[Amount])

For some reason I am getting a text to number conversion error, just troubleshooting now

[gjhannam23]

The formula in itself works, however it doesn't capture the requirement that the billing is recurring. I think I may need to combine the above with https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/td-p/409365 

The following is my attempt at it but it doesn't seem to work;

Monthly Recurring Amount = 
VAR tmpCalendar = ADDCOLUMNS(HeaderDocDate,"Month",MONTH([Date]),"MonthYear",VALUE(YEAR([Date]) & FORMAT(MONTH([Date]),"0#")))
VAR tmpBilling = ADDCOLUMNS(DocumentItems,"MonthYearBegin",VALUE(YEAR([RecurringStartDate]) & FORMAT(MONTH([RecurringStartDate]),"0#")),
                                      "MonthYearEnd",VALUE(YEAR([RecurringEndDate]) & FORMAT(MONTH([RecurringEndDate]),"0#")))
VAR tmpTable = 
SELECTCOLUMNS(
    FILTER(
        GENERATE(
            tmpBilling,
            SUMMARIZE(tmpCalendar,[Year],[Month],[MonthYear])
        ),
        [MonthYear] >= [MonthYearBegin] &&
        [MonthYear] <= [MonthYearEnd]
    ),
    "Customer",'DocumentItems'[DocID],
    "Year",[Year],
    "Month",[Month],
    "Amount",[MonthlyRecurringProfit]
)
RETURN SUMX(tmpTable,[Amount])

I get the error: "[Monthly Recurring Amount] Cannot convert value '' of type text to type number"

 any thoughts?

[v-chuncz-msft · Accepted solution]

@gjhannam23 

The expression below returns an error. Try adding IF inside VALUE.

VALUE ( "" )