Search code examples
excelfilterpowerbidaxsummarize

Sum from two tables with filters in DAX/Power BI

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?

Solution

  • 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])