cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
RohitCelur
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 DateCol 1Col 2Col 3Measure
7/1/20193073202
7/2/2019579123
7/3/20194952215
7/4/2019330025
7/5/20191920207
7/6/2019258027
7/7/2019218007
7/8/201925336013
7/9/201928321620
7/10/201923025126
7/11/201926093534
7/12/201927410337
7/13/20192870037

 

Please help creating a calculation to derive Measure column

 

Thank you

Rohit

 

1 REPLY 1
Super User III
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.

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

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

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘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.