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 🙂

[Tendo]

Hello @v-mdharahman ,

unfortunately I am receiving the following failure:

The “Price” object of type Column already exists in the Next billing object of type table. (it´s translated from german, so i don´t know if failure is in english called the same)

Do you have an idea how to solve the issue?

Thank you

Tendo

[v-mdharahman]

Hi @Tendo,

The error (The 'Price' object of type Column already exists) you're seeing happens because the column Price already exists in your base table (Subscription line table), and the DAX formula tries to add another column with the same name using ADDCOLUMNS.

To fix this, you'll just need to rename the new column in the ADDCOLUMNS function to something like "Billing Price" (or any other unique name). Here's the updated code:

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 PriceValue = '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()
),
"Billing Price", PriceValue // Renamed to avoid collision
)
),
"BillingDateFiltered",
IF ( [Next Billing Date] <= 'Subscription line table'[End Date], [Next Billing Date], BLANK() )
)

Best Regards,

Hammad.

[Tendo]

HI @v-mdharahman ,

I apologize that I have not yet replied. I'm very busy at the moment and had to postpone this topic. I'll take a look at your proposed solution as soon as possible and give you some feedback.

Thank you for your help and best regards

Tendo

[v-mdharahman]

Hi @Tendo,

Thank you for coming back and confirming, please get back to us when you have further update on this issue.

Best Regards,

Hammad.

[v-mdharahman]

Hi @Tendo,
Hope everything’s going smoothly on your end. We haven’t heard back from you, so I wanted to check if the issue got sorted. If yes, marking the helpful solution would be awesome for others who might run into the same thing.
Still stuck? No worries just drop us a message and we can jump back in on the issue.

Best Regards,

Hammad.

[v-mdharahman]

Hi @Tendo,
We noticed there hasn’t been any recent activity on this thread, so I wanted to check if you got a chance to look into the response provided earlier or if the issue got resolved from your end.

If you still need support, just reply here and we’ll pick it up from where we left off.

Best Regards,

Hammad.

[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

[Tendo]

Hello @v-mdharahman ,

finally I have time to check if your solution works.:-)

First of all, thank you very much for your help!!!

I have a question. After "Switch", is Frequency a VAR? It doesn´t work for me.

Thanks a lot and best regards

Tendo 🙂

[Tendo]

@v-mdharahman please forget about it. I found my mistake. Just gave your VAR Frequency a different name in my code... 🙈

[v-mdharahman]

Hi @Tendo,

As we haven’t heard back from you, so just following up to our previous message. I'd like to confirm if you've successfully resolved this issue or if you need further help.

If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. If you find a reply particularly helpful to you, you can also mark it as a solution.
If so, it would be really helpful for the community if you could mark the answer that helped you the most. If you're still looking for guidance, feel free to give us an update, we’re here for you.

Best Regards,

Hammad.