Running Total in Matrix

[Anonymous]

Hi All,

i could able to achieve sum of ll the months.

Provided the example for "sum" of all the columns and we are showing that in right hand side.

Is it possible to have running total? Provided the sample output below

Thanks in Advance

Prakash

[v-alq-msft]

Hi, @Anonymous 

Based on your description, I created data to reproduce your scenario.

Table:

Then you may create a calculated table and build a one-to-many relationship between two tables as follows.

Date Table = CALENDARAUTO()

You can create a measure as below.

Running Total =

CALCULATE(

         SUM('Table'[Value]),

         DATESYTD('Date Table'[Date])

)

Finally, you may use matrix visual to show the reslut:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Mariusz · Accepted solution]

Hi @Anonymous 

Try something like this.

Sales RT = 
IF(
    NOT ISEMPTY( 'Table' ),
    VAR __maxDate = MAX( 'Calendar'[Date] )
    RETURN
    CALCULATE(
        [Sales],
        'Calendar'[Date] <= __maxDate,
        ALL( 'Calendar' )
    )
)

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

[Anonymous]

Hi @Mariusz ,

Can we achieve this without the help of calendar table?

Thanks.

Also any help n the below would be helpful

https://community.powerbi.com/t5/Desktop/create-measure-based-on-column-values/m-p/903165/highlight/...

[Mariusz]

Hi @Anonymous 

You can replace Calendar Date column with a date column Comming from your table, but using a Date Dimension / Calendar Table is considered the best practice.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

[Anonymous]

Hi @Mariusz 

I changed as per your suggestion but it is not working.

Sales RT = 
IF(
    NOT ISEMPTY( 'Table' ),
    VAR __maxDate = MAX( 'Table'[Date] )
    RETURN
    CALCULATE(
        [Sales],
        'Table'[Date] <= __maxDate,
        ALL( 'Table' )
    )
)

The counts are not matching.

Thanks

[Anonymous]

Hi,

@Mariusz Thanks for your help.

Actually i forgot to put AllExcept. Now it is woking fine

Sales RT = 
IF(
    NOT ISEMPTY( 'Table' ),
    VAR __maxDate = MAX( 'Table'[Date] )
    RETURN
    CALCULATE(
        [Sales],
        'Table'[Date] <= __maxDate,
        ALLExcept( 'Table','Table'[Source System] )
    )
)

[Anonymous]

Hi @Mariusz 

I would like to have a rollin 3 months average. will the below code is good? or do we have any better way?

Sales RT = 
IF(
    NOT ISEMPTY( 'Table' ),
    VAR __maxDate = MAX( 'Table'[Date] )
    VAR __minDate = MIN( 'Table'[Date] )-90
    RETURN
    CALCULATE(
        [Sales],
        'Table'[Date] <= __maxDate,'Table'[Date] >= __minDate,
        ALLExcept( 'Table','Table'[Source System] )
    )
)

Thanks 

[amitchandak]

If you create a running total over month and add that to matrix it should work ?

Please refer

https://community.powerbi.com/t5/Desktop/Cumulative-Total/td-p/43115

https://www.daxpatterns.com/cumulative-total/

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601