Performance issue when calculating quantity between two dates

[sgeheeb]

Hi,

I have the following DAX-Code for a measures which calculates the total quantity between two dates:

1: EndDate is the last date in the slicer selection

2: FirstDate is the first date on which a delivery was made

The measures is supposed to be used in a list / matrix to display the quantity of items delivered to a customer. I cannot use the MIN() of the slicer selection, since this is used to filter for sales and deliveries may have been made before that.

This is my code:

Quantity Deliverd = 

//Date of first item delivery to customer
VAR firstItemDelivery = CALCULATE(
    MIN(Shipments[Posting Date]),
        ALL('Posting Date'))
//Last date in selection
VAR endDate = MAX('Posting Date'[Date])

//Calculate change of stock at customer
VAR qtyChangeinPeriod =
    CALCULATE (
        SUM (Shipments[Quantity]),
            ALL('Posting Date'[Date]),
            'Posting Date'[Date] >= firstItemDelivery,
            'Posting Date'[Date] <= endDate
        )
RETURN qtyChangeinPeriod

When I use this in a list with Items and Customers, the computation runs out of memory.

Does anyone have an idea why this is happening? In my optionion this is not an extraordinarily expansive operation.

Cheers

[lbendlin]

Fire up your copy of DAX Studio and examine the query plan for that measure. Check for cross joins. Refactor.