Calculate Invoice - PowerBI

Question

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

Answer 1

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