cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Running Total! Need Help

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

Thank you

Rohit

Super User III

## Re: Running Total! Need Help

Hi, @RohitCelur

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.

Announcements

#### Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

#### MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

#### ‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors