Creating table containing future data based on rules

[mfallmann]

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!

[bhelou]

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.

[mfallmann]

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