Calculation of the amount paid till due dates

[VikKos]

Hello, 

Could you please help me to calculate amounts which were paid by clients till their due dates

I have 2 datasets:

1st dataset contains Sales Order Numbers (unique values) and Due Dates (one due date for each sales order)

2nd dataset contains Sales Order Numbers, multiples dates of payments (clients pay several times on each order) and paid amounts

There are relations between 1st and 2nd dataset on Sales Order Number - one to many

I need to calculate the paid amount from second table, but only before due date from the 1st table.

In Excel I can do it with the followin formula: =SUMIFS(payments!E:E;payments!A:A;[@[Sales Order Number]];payments!C:C;"<="&[@[1st due date]])

But when I try to do the similar formula with DAX there is an error.

My formula - 

Paid till 1st due date = CALCULATE(SUM('Payments (3)'[Paid NET Amount]),'Payments (3)'[Sales Order Number]='Fines on commission2'[Sales Order Number],'Payments (3)'[Payment Date]<='Fines on commission2'[1st due date])

Error - The expression contains multiple columns, but only a single column can be used in a True/False expression that is used as a table filter expression.

Is there any other way to do that?

I'd be grateful for any help!

Thanks. Viktoriia

[v-eqin-msft · Accepted solution]

Hi @VikKos ,

To my knowledge, you seem to confuse the logic of measure and column.

Please take a look at the following formula to see the differences between them.

Sum Column =
VAR _date = 'Table1'[Due Dates ]
VAR _id = 'Table1'[Sales Order Numbers]
RETURN
    CALCULATE (
        SUM ( Table2[Paid NET Amount] ),
        FILTER (
            'Table2',
            'Table2'[Sales Order Numbers] = _id
                && 'Table2'[Payment Date] <= _date
        )
    )

Sum Measure =
CALCULATE (
    SUM ( Table2[Paid NET Amount] ),
    FILTER (
        'Table2',
        'Table2'[Sales Order Numbers] = MAX ( 'Table1'[Sales Order Numbers] )
            && 'Table2'[Payment Date] <= MAX ( 'Table1'[Due Dates ] )
    )
)

Here is the pbox file.

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[VikKos]

Thank a lot for your help! It is working now!

Best regards,

Viktoriia

[lbendlin]

Please provide sample data in usable format (not as a picture).