Running Total! Need Help

[Anonymous]

Hello,

I have a report being created in a direct query mode and have a requirement to calculate a running total between two dates selected by the date slicer. See sample data below,

Col 2 and Col 3 are Calculated Measures

the Measure column is a sum of Col 2 (for a specific date) and Col 3 (running total from start of the date selected through a slicer until that day)

Created Date	Col 1	Col 2	Col 3	Measure
7/1/2019	3073	2	0	2
7/2/2019	579	1	2	3
7/3/2019	4952	2	1	5
7/4/2019	330	0	2	5
7/5/2019	1920	2	0	7
7/6/2019	258	0	2	7
7/7/2019	218	0	0	7
7/8/2019	2533	6	0	13
7/9/2019	2832	1	6	20
7/10/2019	2302	5	1	26
7/11/2019	2609	3	5	34
7/12/2019	2741	0	3	37
7/13/2019	287	0	0	37

Please help creating a calculation to derive Measure column

Thank you

Rohit

[sturlaws]

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.