Feb 28 2021 05:56 AM
Feb 28 2021 07:22 AM
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
Feb 28 2021 07:45 AM
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.
Feb 28 2021 07:51 AM
Feb 28 2021 08:14 AM
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.
Feb 28 2021 08:18 AM
Nov 16 2023 08:42 AM
Nov 16 2023 11:44 AM
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] )
)