cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Reporting on targets with recurring billing cycles

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:

 

DocTypeRecurringBillingCycleRecurringStartDateRecurringEndDateUnitProfitProfitAmount
INVOICEAnnual30/10/2019 00:0017/10/2020 00:00£597005.51
INVOICEAnnual20/12/2019 00:0020/12/2020 00:00£44197005.51
INVOICEAnnual21/01/2020 00:0021/01/2021 00:00£497005.51
INVOICEMonthly01/09/2016 00:0031/08/2019 00:00£397005.51
INVOICEMonthly01/11/2019 00:0031/10/2020 00:00£2697005.51
INVOICEMonthly01/12/2019 00:0001/01/2020 00:00£1,49397005.51
INVOICEQuarterly01/09/2019 00:0031/08/2021 00:00£1,83897005.51
INVOICEQuarterly01/10/2019 00:0001/10/2020 00:00£6097005.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

data model.png

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Reporting on targets with recurring billing cycles

@gjhannam23 

 

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

VALUE ( "" )
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

8 REPLIES 8
Highlighted
Memorable Member
Memorable Member

Re: Reporting on targets with recurring billing cycles

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

Highlighted
Regular Visitor

Re: Reporting on targets with recurring billing cycles

Updated original post based on feedback

Highlighted
Memorable Member
Memorable Member

Re: Reporting on targets with recurring billing cycles

@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

Highlighted
Regular Visitor

Re: Reporting on targets with recurring billing cycles

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

Highlighted
Memorable Member
Memorable Member

Re: Reporting on targets with recurring billing cycles

@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

Highlighted
Regular Visitor

Re: Reporting on targets with recurring billing cycles

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?

 

Highlighted
Regular Visitor

Re: Reporting on targets with recurring billing cycles

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

Highlighted
Community Support
Community Support

Re: Reporting on targets with recurring billing cycles

@gjhannam23 

 

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

VALUE ( "" )
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
Super Users of the Quarter - Q2 2020

Super Users of the Quarter - Q2 2020

Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers!

June 2020 Community Highlights

June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Kudoed Authors