Calculate Invoice

[Gio_rgio_DLTT]

Hi,

i have a dateset like this: 

Date	Contract	Value	Monthly Total	Invoice Date	Amount to 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))

Contract dates without amounts are empty because they are the "Stard date" of the contract, therefore they have no values in previous months. So contract 1 for example, starts on 01/01/2023. The first invoicing takes place on 01/03/2023 and the amount is given by the monthly values between 01/01/2023 and 01/03/2023 (therefore January and February). The second on 01/05/2023 and the range is between 01/03/2023 (previous invoice) and 01/05 2023 and so on..

Can someone help me? Thanks

[Anonymous]

HI @Gio_rgio_DLTT,

Perhaps you can try to use the following measure formula if it help with your scenario:

Amount to be paid =
VAR CurrBillDate = table[Invoice Date]
VAR prevBillDate =
    CALCULATE (
        MAX ( table[invoice Date] ),
        FILTER ( ALLSELECTED ( 'table' ), table[Invoice Date] < CurrBillDate ),
        VALUES ( table[Contract] )
    )
VAR result =
    CALCULATE (
        SUM ( table[Monthly total] ),
        FILTER (
            ALLSELECTED ( 'table' ),
            table[Invoice Date] > prevBillDate
                && table[Invoice Date] <= CurrBillDate
        ),
        VALUES ( table[Contract] )
    )
RETURN
    IF ( ISBLANK ( table[Invoice Date] ) = FALSE (), result + 0 )

Regards,

Xiaoxin Sheng