How to create a calculate table for forecasting services due?

[raphazzz]

Team,

The company I work for offers services through a subscription model. When a service is sold, I need to calculate the number of future services based on the subscription plan. We have monthly, bimonthly, and quarterly subscription options.

For instance, if I sell a new service with a bimonthly subscription and a 12-month contract, I will have the initial service plus five recurring services over the course of 12 months.

Currently, I'm facing an issue with creating a calculated table that includes the customer, subscription model, contract term, and initial service date.

The DAX formula should calculate the date for the next service in each "SP" (Service Projection) column. The rule for calculating the date varies based on the column:

1. SP1: The next service occurs 30 days after the initial service for any subscription type.
   Example: If the initial service is on 01/01, the next service (SP1) will be on 01/31 (30 days later).

2. Other SP columns: The next service occurs 90 days (or according to the subscription, the example is for a quarterly subscription) after the previous service in the same column. Example: If the SP1 service is on 02/02, the next service (SP2) will be on 05/03 (90 days later).

So, the formula calculates the next service date based on these rules for each SP column.

Here it is the formula:

SP6 = VAR date_calc = 
IF (
    'service_Table'[Subs] = "Quarterly",
    'service_Table'[SP5] + 90,
    IF (
        'service_Table'[Subs] = "BIMONTHLY",
        'service_Table'[SP5] + 60,
        'service_Table'[SP5] + 30
    )
)
RETURN

IF(date_calc < 'service_Table'[Contract End],date_calc,BLANK())

The formula has to return BLANK if the next service due falls after the contract end. If you look at the example, it does correctly but after the first blank column, the formula instead of keep returning blank is returning a date. It should return blank to all remaining columns (SP7 and beyond has no formulas, hence the blank is not because of the formula).

How to fix that?

Thanks!

[lbendlin]

Stuff like this is much better handled in PowerQuery.  But in general you will want to use GENERATESERIES or its equivalents to create the amortization date points.

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...