Creating cumulative (running) total which responds to date slicer

[dancarr22]

Hello,

I am trying to create a cumulative total which responds to dates changed in a date slicer.

I did read several other posts about this -- all of which had the same DAX formula:

calc_RunningTotalPrincipal = CALCULATE(SUM(Transaction[Principal]),FILTER(ALL(Transaction[TradeDate]),Transaction[TradeDate] <= MAX(Transaction[TradeDate])))

But, this is not working properly. 

I created a simple example with several records.  There is a date, transaction type and amount.  i.e. 1/1/2017, BUY, $100,  Want a running sum of amount based on filters applied -- generally a date slicer.  Though curious, if we add other filters -- ie. transaction type -- do we need to define this in the DAX forumla?

Please see screenshots below as example.

Any assistance greatly appreciated.

Thanks,

Dan

All Dates

With Date Filter

[Eric_Zhang · Accepted solution]

@dancarr22

You could try a measure as below.

Measure =
VAR sumTotal4previousDates =
    CALCULATE (
        SUM ( yourTable[Principal] ),
        FILTER ( ALLSELECTED ( yourTable ), yourTable[date] < MAX ( yourTable[date] ) )
    )
VAR sumTotal4currentDayBuyTrans =
    CALCULATE (
        SUM ( yourTable[Principal] ),
        FILTER ( yourTable, yourTable[transtype] = "Buy" )
    )
VAR sumTotal4currentDayBuyAndSellTrans =
    CALCULATE (
        SUM ( yourTable[Principal] ),
        ALLEXCEPT ( yourTable, yourTable[date] )
    )
RETURN
    IF (
        LASTNONBLANK ( yourTable[transtype], "" ) = "Buy",
        sumTotal4previousDates + sumTotal4currentDayBuyTrans,
        sumTotal4previousDates + sumTotal4currentDayBuyAndSellTrans
    )

[dancarr22]

Thanks Eric!