Hello,
I am struggling with running totals in pivot tables. Our system provides me with a list of records, each of them containing:
- project id + project name,
- business segment the project belongs to
- month
- budget for that specific month
- actual spendings for that month
There are many more fields, by for the purpose of this question I have simplified and anonymised this.
Now I want to build a monthly report that shows the list of actual projects, the spendings and budget for that month AND the year-to-date spendings and budget. Please see the attached file for an example. The actual month numbers are easy, the YTD are the problem for me.
Since you provided a simplified example, the solution is also rather simplified (attached).
For the YTD, I copied your pivot table, removed the Month filter but added a slicer for the Month. Now you can visibly easily select months 1 and 2 to view the YTD figures. I would have preferred a TimeLine but that wasn't possible as your model does not include real dates, just month numbers. Finally the Variance % is a simple calculated field.
Having said that, if your actual model has real dates (and you are not using Excel for the Mac), and perhaps historical data, consider loading the data into the Data Model (i.e. Power Pivot). With the right set-up you can then write DAX measures using powerful time-intelligent functions to calculate year-to-date amounts or same-period-last-year etc.
Many thanks Riny. I'm afraid I didn't formulate my question precise enough. The challenge was to have both the YTD AND the actual month numbers in the same table.
DAX is new to me, but it seems to be an interesting a new world to dive into.
