Set up the Subscription Billing Power BI app

In a subscription business, managers typically want to know KPIs such as Monthly Recurring Revenue (MRR) or Total Contract Value (TCV), because these metrics provide insight into predictable revenue streams, long-term customer commitments, and the overall health of the business model.

To calculate these KPIs in Business Central, more is needed than just the current state of your subscription contracts and subscription contract lines. Subscriptions can change over time. Quantities might increase, terms might shorten, or subscriptions might end. If you only evaluate the live subscription contract data, you only get the recurring revenue for the current month, without any historical perspective.

Relying on posted invoices or general ledger entries doesn't solve this challenge either. While they provide historical figures, they don't include future-dated subscriptions that aren't invoiced but still need to be reflected in recurring revenue and contract value calculations.

The Subscription Contract Analysis Entries table creates a monthly snapshot of all active and planned subscriptions. This snapshot ensures you can:

• Track KPIs historically, even when contracts changed.
• Include future contracts in your revenue calculations.
• Maintain a consistent, comparable basis for both current and historic subscription analysis.

Setting up the analysis entries is therefore a required step to make full use of the subscription billing Power BI app.

You can also use the Subscription Contract Analysis Entries table to easily create an ad-hoc analysis based on your subscription data.

Create subscription contract analysis entries

You can create subscription contract analysis entries manually:

1. Select Search (Alt+Q) in the upper-right corner, enter Create Subscription Contract Analysis, then choose the related link.
2. The task runs immediately and generates entries for all contract lines that meet the required conditions.
3. Select Search (Alt+Q) in the upper-right corner, enter Subscription Contract Analysis Entries, then choose the related link. The newly created entries show the current date as the analysis date.

The task creates one entry per subscription line for the current month. It only considers subscription lines that meet the following conditions:

• The line is linked with a customer or vendor subscription contract.
• The subscription line is active, meaning the Subscription Line End Date field is empty or has a future date.

For each entry, Business Central calculates the Monthly Recurring Revenue LCY (MRR) and Monthly Recurring Cost LCY (MRC).

It calculates the MRR for a customer-facing subscription by normalizing the amount (LCY) to a monthly value based on the date formula in the Billing Base Period field.

Example calculation of MRR:

• A subscription line has an amount of 1.200 USD with a billing base period of 1Y (or 12M). The amount represents a yearly subscription price.
• Business Central calculates the MRR by dividing 1.200 USD by 12 months, resulting in 100 USD per month.

The value for MRC is calculated in a similar way based on the unit cost (LCY) and billing base period of the subscription.

MRR and MRC for vendor-facing subscription lines are calculated in the following way:

• MRR is always zero.
• MRC is calculated by normalizing the amount (LCY) to a monthly value based on the date formula in the Billing Base Period field.

Set up job queue for subscription contract analysis entries

Instead of running the task manually each month, you can schedule it in the job queue. The schedule ensures that your subscription contract analysis entries are always created on time and stay up to date.

1. Select Search (Alt+Q) in the upper-right corner, enter Job Queue Entries, then choose the related link.
2. Choose the New action.
3. In the Object Type to Run field, choose Report.
4. In the Object ID to Run field, choose 8005, Create Subscription Contract Analysis.
5. In the Earliest Start Date/Time field, enter the first date of the next month.
6. In the Next Run Date Formula field, enter 1M.
7. In the Starting Time field, enter 2 AM.
8. Choose the Set Status to Ready action.

Now, the subscription contract analysis entries are created one time at the beginning of each month. You can run the job queue entry more than once in the same month. In this case, it only creates entries for new subscription lines that you added after the last run.

By automating this process, you reduce manual effort and ensure that the Power BI Subscription Billing app always reflects the most accurate and complete set of subscription data.

Set up job queue for subscription contract termination dates

To calculate KPIs such as Total Contract Value (TCV) or Sales Forecast, the Power BI Subscription Billing app relies on the Term Until field to determine the remaining subscription period. This date is calculated in Business Central based on the Subscription Start Date, Initial Term, Notice Period, and Extension Period defined on the subscription line.

To ensure the Term Until field is always accurate, you can schedule the update as a recurring job queue entry:

1. Select Search (Alt+Q) in the upper-right corner, enter Job Queue Entries, then choose the related link.
2. Choose the New action.
3. In the Object Type to Run field, choose Codeunit.
4. In the Object ID to Run field, choose 8058, Update Sub. Lines Term. Dates.
5. In the Earliest Start Date/Time field, enter the first date of the next month.
6. In the Next Run Date Formula field, enter 1D.
7. In the Starting Time field, enter 1 AM.
8. Choose the Set Status to Ready action.

Now, the notice dates update automatically one time a day and outside of business hours.

Related information

Cancel planned subscription lines
Subscription lines
Use Job Queues to Schedule Tasks
Installing Power BI apps for Business Central
Subscription Billing controlling
Subscription Billing overview