running total in pivot table when grouping dates

[Elenazhilina]

in my file there are sums in the context of dates. I formed a pivot table where in the rows of the date, in the columns of the sum. I need to output the running total. for this, the field was re-displayed the amount and made an additional calculation. everything is considered fine, but as soon as I group the data by months, the calculation of the cumulative total gets confused. tell me how to keep the running total in the pivot table after grouping dates into years and months?

[#2]

@Elenazhilina 

With grouping the date built-in Running Total resets calculations from the beginning of each group. To calculate running total through all dates you need to add data to data model and create the measure to calculate it.

On such sample

Creating PivotTable add data to data model

add two measures

Sum Value:=SUM(Table1[Value])

and

Value Running Total:=VAR
MaxDate=MAX ( Table1[Date] )
RETURN
  CALCULATE([Sum Value],
             Table1[Date]<= MaxDate,
             ALL (Table1)
  )

Instead of first one you may use implicit measure which sums value, but better to keep everything in hands and use explicit measures.

In second one MaxDate will be maximum date for the current group, removing all other filters from the Table we calculate sum of Value till MaxDate.

Result is

[#3]

So I need to have Pivot?

[#4]

@Elenazhilina 

Power Pivot is desirable but not necessary. However, your version of Excel shall support data model. That is Excel for Windows Desktop starting from 2013 or 2010.

[#5]

thank you very much for your answer) I've been struggling with this for the second week. can i show you my file?
it's hard for me to correctly explain and understand you in English ((
I have an array of data. I have compiled it into the pivot table I need. in rows, grouping by counterparties and, as another level, dates. but since the array will grow, I really need a grouping by years and months, so that the cumulative total is visible.

[#6]

@Elenazhilina 

In general yes, but first please try on copy of your file if you are ready to add data to data model. On any PivotTable click on More fields

Excel asks to create new PivotTable

Click Yes, new PivotTable will be created and data added to data model. But you will lost all PivotTable formatting and shall format/group/filter/add slicers from scratch. That's time consuming operation, but better than create new PivotTable from scratch.

Thus I'd suggest you to try on one PivotTable, if it's worth to do such way I'll help to create the measure.

[#7]

I will be very grateful to you. never faced pivot. tomorrow I will definitely try to figure it out. if it doesn't work, with your permission, I'll show you the file. it is very important to get the result

[#8]

I am very curious how you would write the measures to do this with multiple categorical columns. In the above sample your row labels are the dates, but if you want a running total measure for each of two columns called "Sum of Value - GroupA" and "Sum of Value - GroupB".

I do not see grouping function within the calculate when you create the measure.

[#9]

@Dweesil 

Instead of removing all filters with ALL() you may remove all but Group filter with ALLEXCEPT()

RTotal:=VAR
MaxDate=MAX ( Table1[Date] )
RETURN
  CALCULATE( [Total],
       Table1[Date]<= MaxDate,
       ALLEXCEPT( Table1, Table1[Group] )
  )