Forum Discussion
Creating table containing future data based on rules
Dear All,
I need to pick your brains on this one:
I have a table containing contract Id, contract value, contract starting date and invoice frequency (monthly, bimonthly, quarterly, every 6 months , yearly)
Based on this data, I would like to be able to display all expected invoices in the coming x years.
My first idea on achieving this was to create a new table based on the input I have in my contract table.
Would anyone of you have a suggestion how I can get this done?
Many thanks in advance!
2 Replies
ve this a try :
Expected Invoice =
var start_date = MIN(Contracts[Starting Date])
var invoice_period = MAX(Contracts[Invoice Period])
var invoice_value = MAX(Contracts[Contract Value])
return
ADDCOLUMNS(
FILTER(
CALENDAR(start_date, DATEADD(MAX(Calendar[Date]), X, MONTH)),
MOD(DATEDIFF([Date], start_date, MONTH), invoice_period) = 0
),
"Contract ID", MAX(Contracts[Contract ID]),
"Invoice Value", invoice_value
)
Replace "X" in the formula with the number of months you want to display the invoices for.Thanks for the fast response.
However, this way I get a table with just one contract ID, and all days of the months we should invoice until X is reached ...
I am looking for a table containing only contracts with the dates they are expected to be invoiced ...
Every contract can have a different invoice starting date, and invoicing period
This is an example of what I have (the actual no of contracts will be in the mid-hundreds):
Contract ID Starting Date IF frequency (months) Contract 1 01.01.2022 4 Contract 2 01.05.2022 6 This is what I expect:
Contract ID IV Date Contract 1 01.01.2022 Contract 1 01.05.2022 Contract 1 01.09.2022 Contract 1 01.01.2023 Contract 1 01.05.2023 Contract 1 01.09.2023 Contract 1 01.01.2024 Contract 1 01.05.2024 Contract 1 01.09.2024 Contract 2 01.11.2022 Contract 3 01.05.2023 Contract 4 01.11.2023 Contract 5 01.05.2024 Contract 6 01.11.2024
