Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Running Total in Matrix

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...
Need Help
  • Mariusz's avatar
    Mariusz
    6 years ago

    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.

     

Mariusz's avatar
Mariusz
Community Champion
6 years ago

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's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        Anonymous
        Not applicable
        6 years ago

        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