cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Stuznet Member
Member

Show Cumulative Running Total From Previous and current month

Hi all, 

 

I'm currently struggling to get this data to show up on the current month, not the whole 12 months.

 

Here is my dataset

MonthValueCumulative
January6363
February55118
March30148
April33181
May50231
June60291
July69360
August64424
September60484
October15499
November14513
December13526
 5263838

 

This the formula I created. The total is the sum of Value column,

 

Cumulative = CALCULATE([Total],FILTER(ALLSELECTED('Table1'),''Table1'[Month Num] <= MAX(''Table1'[Month Num])))

So how do I get it to display from January to current month? 

 

Thanks Smiley Happy

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Stuznet Member
Member

Re: Display Cumulative Running Total from Previous up to Current Month

I figured it out how to stop the running total. 

 

Measure = 
VAR Test = CALCULATE([Total] , 
             FILTER(
                    ALLSELECTED(Table1) , 
                    Table1[Month Number] <= MAX(Table1[Month Number])))
RETURN
      IF(MAX(Table1[Month Number]) +1 <= MONTH(TODAY()),
         Test, BLANK())

 

3 REPLIES 3
Super User
Super User

Re: Cumulative Rolling 12 Month

Hi @Stuznet,

 

Not really sure on the rest of the model and if you have date instead of month names, but looking at your data and if you want it to have the value until current month without the selection of any slicer you should redo your measure to something like this:

 

Cumulative =
CALCULATE (
    [Total],
    FILTER ( ALLSELECTED ( 'Table1' ), 'Table1'[Month Num] <= MONTH ( TODAY () ) )
)

Again your data is insuficient to test with your setup but should work.

 

Regards,

MFelix

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Stuznet Member
Member

Re: Cumulative Rolling 12 Month

@MFelixThe formula you provided doesn't give me the correct result. It basically sum the total from Jan to oct and displaying the total in the column. 

 

On my original data I do not have any date which make it difficult for me to create a date table. (Any suggestion?)

The only date I have is the Month Names table. I added an index column as a Month Number. 

 

I would like to display the data from January to present month. So the next data dump, will be automatically show up on the chart and without selection of any slicer. 

Stuznet Member
Member

Re: Display Cumulative Running Total from Previous up to Current Month

I figured it out how to stop the running total. 

 

Measure = 
VAR Test = CALCULATE([Total] , 
             FILTER(
                    ALLSELECTED(Table1) , 
                    Table1[Month Number] <= MAX(Table1[Month Number])))
RETURN
      IF(MAX(Table1[Month Number]) +1 <= MONTH(TODAY()),
         Test, BLANK())