Forum Discussion
Creating future dates with values in new table
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.
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
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.
Hello Anonymous ,
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 🙂
Anonymous please forget about it. I found my mistake. Just gave your VAR Frequency a different name in my code... 🙈
