March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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!
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 |
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
90 | |
86 | |
83 | |
72 | |
49 |
User | Count |
---|---|
167 | |
149 | |
99 | |
73 | |
57 |