Restore original filter context in DAX formula

Question

I have a data model with a table called Transactions (below), and TranDate which is the date dimension.

I have a measure to calculate the opening balance for the first date that is visible in the pivot table.

Op Bal First Selected := 
VAR MinDate = CALCULATE ( 
    MIN ( TranDates[Tran Date] ), 
    ALLSELECTED ( TranDates ) 
)
RETURN
CALCULATE (
    SUM ( Transactions[Amount] ),
    FILTER
    (
        ALL ( TranDates ),
        TranDates[Tran Date] < MinDate
    )
)

If I deselect Jan-16, I get the desired result below:

However, the measure only works in Excel 2016 and not Excel 2013, because variables are not supported in Excel 2013. If I replace the MinDate variable with the DAX expression, then the measure will return blanks because ALLSELECTED restores the context in the FILTER() function and not the context in the CALCULATE() function (i.e. MinDate is 1-Jan instead of 1-Feb).

Is there an equivalent DAX formula that works in Excel 2013 / SSAS 2014 (i.e. without using variables)?

Christopher Tso Christopher Tso · Accepted Answer · 2016-05-16T21:52:14

After lots of trial and errors, here's the measure I defined:

Op Bal First Selected:=
MINX
(
    ADDCOLUMNS (
        ADDCOLUMNS
        (
            SUMMARIZE (
                ALL(Transactions),
                Transactions[Tran Date]
            ),
            "MinDate",
            CALCULATE ( 
                MIN ( Transactions[Tran Date] ),
                ALLSELECTED()           
            )
        ),
        "Op Bal First Selected Calc",
        CALCULATE (
            SUM ( Transactions[Amount] ),
            FILTER 
            (
                ALL( Transactions ),
                Transactions[Tran Date] < [MinDate]
            )
        )               
    ),
    [Op Bal First Selected Calc]
)

I used ALL() in the inner context so that the original filter context (i.e. pivot table selection) is preserved in the context just outside of ALLSELECTED().

If you want to use the TranDate table as the filter, you can define it as follows:

Op Bal First Selected:=
MINX
(
    ADDCOLUMNS (
        ADDCOLUMNS
        (
            SUMMARIZE (
                ALLSELECTED(TranDates),
                TranDates[Tran Date]
            ),
            "MinDate",
            CALCULATE ( 
                MIN ( TranDates[Tran Date] )        
            )
        ),
        "Op Bal First Selected Calc",
        CALCULATE (
            SUM ( Transactions[Amount] ),
            FILTER 
            (
                ALL( TranDates ),
                TranDates[Tran Date] < [MinDate]
            )
        )               
    ),
    [Op Bal First Selected Calc]
)

Restore original filter context in DAX formula

1 Answers