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.

     

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