I have a dataset like this:
Date | Contract | value | Monthly total | Invoice Date | Amount to be paid |
---|---|---|---|---|---|
01/01/2023 | Contract 1 | 180 | 380 | 01/01/2023 | |
01/01/2023 | Contract 1 | 200 | 380 | 01/01/2023 | |
01/02/2023 | Contract 1 | 800 | 1000 | ||
01/02/2023 | Contract 1 | 200 | 1000 | ||
01/03/2023 | Contract 1 | 150 | 550 | 01/03/2023 | 1380 |
01/03/2023 | Contract 1 | 400 | 550 | 01/03/2023 | 1380 |
01/04/2023 | Contract 1 | 350 | 950 | ||
01/04/2023 | Contract 1 | 600 | 950 | ||
01/05/2023 | Contract 1 | 220 | 330 | 01/05/2023 | 1500 |
01/05/2023 | Contract 1 | 110 | 330 | 01/05/2023 | 1500 |
01/01/2023 | Contract 2 | 50 | 150 | 01/01/2023 | |
01/01/2023 | Contract 2 | 100 | 150 | 01/01/2023 | |
01/02/2023 | Contract 2 | 200 | 350 | 01/02/2023 | 150 |
01/02/2023 | Contract 2 | 150 | 350 | 01/02/2023 | 150 |
01/03/2023 | Contract 2 | 100 | 300 | 01/03/2023 | 350 |
01/03/2023 | Contract 2 | 200 | 300 | 01/03/2023 | 350 |
The value column the monthly value, while the "Monthly total" column tells me the monthly total (given by the sum of the values over the same month). For example, Contract 1, date 01/01/2023, 380 "monthly total"(180+380). The "Invoice Date" column tells me the date on which the billing is to take place.
I should find a way to calculate the amount to be paid for each billing date (Column "Amount to be paid"). For example: Contract 1; Invoice Date 01/03/2023; Amount to be paid: 1380 (1000 of 01/02/2023 + 380 of 01/01/2023).
So, with respect to the invoice date, i have to consider the months before until the previous billing.
I tried something like this thanks to another post, but it doesn't work:
Amount to be paid =
var thisBillDate = table[Invoice Date]
var prevBillDate = CALCULATE(
MAX(table[invoice Date]),
ALLEXCEPT(table, table[Contract]),
table[Invoice Date] < thisBillDate
)
var result = CALCULATE(
SUM(table[Monthly total]),
ALLEXCEPT(tale, table[Contract], table[Date]),
prevBillDate < table[Invoice Date] && table[Invoice Date] <= thisBillDate)
return IF(NOT ISBLANK(table[Invoice Date]), COALESCE(result, 0))
Can someone help me? Thanks
Assuming you have two tables:
Table: Bill
Date | Contract | value |
---|
Table: Invoice
Contract | Invoice Date |
---|
Add a Calculated Column to table Invoice
with:
Amount to be paid =
var thisContract = [Contract]
var thisInvoiceDate = [Invoice Date]
var prevInvoiceDate = CALCULATE(
MAX(Invoice[Invoice Date]),
ALLEXCEPT(Invoice, Invoice[Contract]),
Invoice[Invoice Date] < thisInvoiceDate
)
var result = CALCULATE(
SUM(Bill[value]),
Bill[Contract] = thisContract &&
Bill[Date] >= prevInvoiceDate &&
Bill[Date] < thisInvoiceDate
)
return result