Filling invoice amount paid depending on a sum of total payments

[dechernov]

Hi all,

I am new to Power BI and would like to get a little help with DAX expression. I have two tables which have no relationships between them: “Invoices” and “Payments made”. I want to create a column called “Paid amount” in the first table which calculates if we can pay a full amount of invoice or a part depending on a total sum of payments made and puts corresponding value. It seems that I should use rolling sums, but I stucked at developing a logic of an expression. Both tables involve updating. Anyone who can help me is very grateful.

“Invoices” table looks like this:

“Payments made” table looks like this:

Expected result:

[v-shex-msft · Accepted solution]

HI @dechernov,

I'd like to suggest you write measure formula to lookup and summary table records based on current date values:

Rolling Payment=
VAR currDate =
    MAX ( Invoices[Date] )
RETURN
    SUMX (
        FILTER ( ALLSELECTED ( Payments ), Payments[Date] <= currDate ),
        Payments[Paid Amount]
    )

Regards,

Xiaoxin Sheng