Running Total! Need Help

Hi, Anonymous 

 

I am a bit confused about what your desired outcome is. You want to have a table visual, right? 

 

[Created date] and [Col 1] are fine, but I don't understand how Col 2 and Col 3 relates to [Col 1], except that you write that they are measures. And then you have a third measure you call measure, right?

 

In general, when you want to have a table visual with dates, and have a running total determined by a slicer, you need to have a separate date-table, without any relationship to the main table.

 

You can then write your query like this:

Running Total =
VAR _minDate =
    CALCULATE ( MIN ( Dates[Date] ) )
VAR _currentDate =
    CALCULATE ( SELECTEDVALUE ( Table[Created date] ) )
RETURN
    CALCULATE (
        SUM ( Table[Col1] );
        FILTER (
            ALL ( Table );
            Table[Created date] <= _currentDate
                && Table[Created date] >= _minDate
        )
    )

 

The dates used in the table visual should not be from this special stand alone date-table, but from the main-table(or date dimension/table if your model has that). This will result in all rows showing, but rows outside the range specified by the slicer will have blank value for the running total. These rows can be filtered out by creating this measure:

AuxFilterMeasure =
VAR _maxDate =
    CALCULATE ( MAX ( Dates[Date] ) )
VAR _minDate =
    CALCULATE ( MIN ( Dates[Date] ) )
RETURN
    IF (
        SELECTEDVALUE ( Table[Created date] ) <= _maxDate
            && SELECTEDVALUE ( Table[created date] ) >= _minDate;
        1;
        0
    )

and set it to filter=1 in the filter pane of the visual.

 

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.