Creating future dates with values in new table

[Tendo]

Hey Folks,

hope somebody can help me with little problem. 😊

My datamodel is based on 5 Tables, 1 Calendar table, 3 fact tables and one dimension table.

In this data model I can display in reporting table the customer (Customer subscription contract table), starting and end date of the subscription, price, subscription contract number  (Subscription line table)…. I also can display the next billing date (Dimension table). Billing date is defined by ERP-System and changes after the invoice run. Measure to display the next billing date:

FutureBillingDate:=
VAR Date = Calculate(Max(Dimension table[Dimensionvaluecode]),Dimension table (Dimensioncode]= “Date »)
Return Date

Example of my displayed table in PowerBI based on the data model:

Customer	Starting Date	End Date	Price	Subscription Contract number	Next billing date	Payment frequency
Santa Claus	01.01.2025	31.12.2030	100€	C00001	25.06.2026	Yearly
Rudolph	01.01.2025	31.12.2028	10€	C00002	01.07.2025	monthly

My goal is to have a new table which shows the next billing date for the next ten years.

Customer	Subscription contract number	Next Billing Date	Price
Santa Claus	C00001	25.06.2026	100€
Santa Claus	C00001	25.06.2027	100€
Santa Claus	C00001	25.06.2028	100€
Santa Claus	C00001	25.06.2029	100€
Rudolph	C00002	01.07.2025	20€
Rudolph	C00002	01.08.2025	20€
Rudolph	C00002	01.09.2025	20€

The next billing date, which is based in our ERP-System, will be overwritten after the invoice run for e.g. C00001, Santa Claus. In 2027 Santa Claus starting point is for next billing date is 25.06.2027. Then the table need to look like:

Customer	Subscription contract number	Next Billing Date	Price
Santa Claus	C00001	25.06.2027	100€
Santa Claus	C00001	25.06.2028	100€
Santa Claus	C00001	25.06.2029	100€
Santa Claus	C00001	25.06.2030	100€

The same logic applies to Rudolph based on his conditions.

I hope that I have been able to explain my problem and goal clearly and comprehensibly and that someone may be able to help me.

I am grateful for any help in finding a solution

Tendo 🙂

[v-mdharahman]

Hi @Tendo,

Thanks for reaching out to the Microsoft fabric community forum.

To achieve a dynamic table that displays the next billing dates for the next 10 years per customer and subscription, based on the current next billing date from your ERP, you'll need to generate a calculated table in Power BI using DAX that reads the current "Next Billing Date" (from your Dimension table) and checks the payment frequency (monthly, yearly, etc.). It should Iteratively adds future billing dates based on that frequency and stops at either the contract end date or a 10-year max window.

DAX:

NextBillingSchedule =
VAR MaxYears = 10
RETURN
ADDCOLUMNS (
GENERATE (
FILTER (
'Subscription line table',
NOT ISBLANK ( [FutureBillingDate] )
),
VAR CustomerName = RELATED ( 'Customer Subscription contract table'[Customer] )
VAR ContractNumber = 'Subscription line table'[Subscription Contract number]
VAR Price = 'Subscription line table'[Price]
VAR StartDate = [FutureBillingDate]
VAR EndDate = 'Subscription line table'[End Date]
VAR Frequency = 'Subscription line table'[Payment frequency]
VAR MaxDate = MIN ( EndDate, EDATE ( StartDate, 12 * MaxYears ) )
RETURN
ADDCOLUMNS (
GENERATESERIES ( 0, MaxYears * 12, 1 ),
"Customer", CustomerName,
"Subscription Contract number", ContractNumber,
"Next Billing Date",
SWITCH (
TRUE (),
Frequency = "Yearly", DATEADD ( StartDate, [Value], YEAR ),
Frequency = "Monthly", DATEADD ( StartDate, [Value], MONTH ),
BLANK()
),
"Price", Price
)
),
"BillingDateFiltered",
IF ( [Next Billing Date] <= 'Subscription line table'[End Date], [Next Billing Date], BLANK() )
)

This will create a table with one row per future billing date per contract. You can further filter BillingDateFiltered in your report to remove blank rows. And fi your frequencies vary (quarterly, semi-annually, etc.), you can expand the SWITCH logic accordingly.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.
Community Support Team