Cumulative sum in PowerPivot

[Maxiac1]

Hi everybody,
Tried for a while now, but I couldn’t solve the following issue:
I have a data table in Excel, which looks like the following:


What I’d like to have is this:


I encountered the following issue:

• When I filter for the current YearMonth (in my example 201602), the row “Apple” disappears, as there is no sales of apples in 201602. However, there is a history of sales for apples, which are cumulated (Column: Sales of Country to Date). Also, there is a forecast, which is the total of past sales and future sales of apples (Colomn: Sales of Country Forecast).

Thank you in advance.

 

[James02]

Power Pivot will read your filter first. So it reads 201602 & pulls in Banana from there. Apple isn't even looked at, regardless that there is past history. If you want the data from the table you specified, I would create a table with a slicer for "date". Then you select the date range to give you the corresponding Product sales total. Just how I would do it personally. Thanks!

 

[Maxiac1]

James, thanks for your reply!
Unfortunately, a slicer is not an option, as I need to format the Pivot table (colors, fonts, borders, etc.) to fit a certain template.
Any other possible way to solve this? Any help is very much appreciated! Thanks!