Sum from two tables with filters in DAX/Power BI

Question

I've got two tables.

The 1st one contains SKU, client, order date, order amount.
The 2nd one contains SKU, client, promo id, promo start date, promo end date.

I need to calculate ordered amount for each client-promo-SKU.

In Excel I would have something like:

SUMIFS(
    TABLE1[ORDERED],
    TABLE1[CLIENT], TABLE2[CLIENT],
    TABLE1[ORDER DATE], ">="&TABLE2[ORDER START DATE], 
    TABLE1[ORDER DATE], "<="&TABLE2[ORDER END DATE], 
    TABLE1[SKU], TABLE2[SKU]
)

Table 1

Table 2

Excel formula. Cells K2 and K3

I've already figured out how to filter the first table by date (the connection between ORDERS and PromoLib tables is SKU, many to many):

ordered = CALCULATE(
    SUM(ORDERS[order qty]),
    KEEPFILTERS(DATESBETWEEN(ORDERS[Document Date], PromoLib[Order Start], 
        PromoLib[Order End])),
)

How can I add filters to this DAX formula so it will filter the ORDERS table by client?

Jenn Jenn · Accepted Answer · 2021-08-28T02:27:23

The best practice would be to have a Clients dimension table, a Dates dimension table, an Orders fact table and a PromoLib fact table. Then the measures would be much simpler. That being said, here is the syntax for the CALCULATE function:

CALCULATE(<expression>[, <filter1> [, <filter2> [, …]]])

You can add as many filter arguments as needed, so you could add another argument for a client filter like this:

FILTER (Orders, Orders[Client] = PromoLib[Client])

Sum from two tables with filters in DAX/Power BI

1 Answers