DAX Cumulative Total With Date Filters

Question

I am trying to calculate a running total where orders are only valid during a certain date range. Each order has a value, a start date and an end date. I want to calculate the cumulative sum of the order's values only during the dates between an order's start date and end date.

I've read over this article on cumulative totals and have an equation for the running total but I can't figure out how to filter the equation so that it filter's out an order once the date table is past the order's End Date. The current measure I have is Cumulative Value:=CALCULATE(SUM(Orders[Vaue]), FILTER(ALL('Date'), [Date] <= MAX([Date]))) and I want to add a filter that filters out any orders with an end date past the current date row, similar to this Filter('Order', 'Orders'[Order_End_Date] < 'Date'[Date]). When I try to add this filter though I get an error since 'Date'[Date] is not used in any aggregation.

Below is the data model that I am using and a link to the Excel File with the data model.

The sample Data:
+-----------+ | Date | +-----------+ | 1/1/2015 | | 1/2/2015 | | 1/3/2015 | | 1/4/2015 | | 1/5/2015 | | 1/6/2015 | | 1/7/2015 | | 1/8/2015 | | 1/9/2015 | | 1/10/2015 | +-----------+

+----------+------+------------------+----------------+ | Order_Id | Vaue | Order_Start_Date | Order_End_Date | +----------+------+------------------+----------------+ | 1 | 1 | 1/1/2015 | 1/3/2015 | | 2 | 3 | 1/2/2015 | | | 3 | 6 | 1/3/2015 | 1/7/2015 | | 4 | 7 | 1/5/2015 | | +----------+------+------------------+----------------+

And the output of the current measure I have and what the correct measure's output should be.
+-----------+-----------------+--------------------------+ | Date | Current Measure | Desired Measure's Output | +-----------+-----------------+--------------------------+ | 1/1/2015 | 1 | 1 | | 1/2/2015 | 4 | 4 | | 1/3/2015 | 10 | 9 | | 1/4/2015 | 10 | 9 | | 1/5/2015 | 17 | 16 | | 1/6/2015 | 17 | 16 | | 1/7/2015 | 17 | 10 | | 1/8/2015 | 17 | 10 | | 1/9/2015 | 17 | 10 | | 1/10/2015 | 17 | 10 | +-----------+-----------------+--------------------------+

greggyb greggyb · Accepted Answer · 2015-10-07T14:53:31

Cumulative Value2:=CALCULATE(
    SUM(Orders[Vaue])
    ,FILTER(
        VALUES(Orders[Order_Start_Date])
        ,Orders[Order_Start_Date] <= MAX('Date'[Date])
    )
    ,FILTER(
        VALUES(Orders[Order_End_Date])
        ,ISBLANK(Orders[Order_End_Date])
            || Orders[Order_End_Date] >= MAX('Date'[Date])
    )
)

Model Diagram (note I took out your date relation - for the limited use case you've provided, it only makes things more complicated):

Note: I will refer to function arguments positionally, with the first argument represented by (1).

So, what we're doing is similar to what you were trying. We've got two FILTER()s, each as an argument to our CALCULATE(). CALCULATE() combines its arguments (2)-(n) in a logical and.

The first FILTER() does essentially what you were already doing, except we are filtering the distinct values of the [Order_Start_Date], comparing them against the current filter context of the pivot table.

The second FILTER() loops over the distinct values of [Order_End_Date], checking two conditions combined in a logical or. We must handle the case of a BLANK [Order_End_Date]. This BLANK is normally implicitly converted to 0 == 1899-12-30, which is less than any date we're considering. In the case of a BLANK, we get a true value from ISBLANK() and the row is returned as a part of FILTER()'s resultset. The other test is simply checking that [Order_End_Date] is greater than the current filter context date in the pivot.

DAX Cumulative Total With Date Filters

2 Answers