Log In
Register
Home
Active Member
February 17, 2023
Offline
I have followed the excellent article here: Power Pivot Running Total to produce a running total measure:
=CALCULATE(SUM('Pile Log'[No of Piles]), FILTER(ALLSELECTED('Calendar'[Date]), ISONORAFTER('Calendar'[Date], MAX('Pile Log'[Piling date]), DESC))),
however, when there are periods in my data set without data and so the chart/pivot table stops - I would like the running total to carry on even through these periods.
How can I achieve this?
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Hi David,
Can you please provide a sample file that replicates this problem?
Active Member
September 21, 2023
Offline
I have followed the same example That David referred to on May 18, 2023. My exact case is about budgeting and follow up for registered hours and (calculated) cost and not sales as in the example Power Pivot Running Total
The principle can be illustrated by using the same spreadsheet. Let's say there is no actual sales for August 2020 and no forecast or budget either for some reason. (for my example with registered hours that could be due to vacation).
If period 2020-08-01 is removed from the data tables, leaving a gap in the data, the running total also gives a gap in the pivot chart. The Excel built in 'Show Values as Running Total In...' compensates for this by using last months values for the missing period(s) in the data set.
As the measure in Power Pivot Running Total is presented as an improvement to Excel's built in running total, can the measure be improved to also use last periods running total value for missing periods in the data set?
Attached is the same spreadsheet used in Power Pivot Running Total. Delete the rows representing August 2020 from the three tables and there will be a gap in the data set and in the pivot chart.
Can this be solved by improving the measure?
Best Regards
David
Moderators
January 31, 2022
Offline
There is no attachment. Please try again and don't forget to press "Start upload" before you submit your reply.
Active Member
September 21, 2023
Offline
Got an error message when i posted the last message, the attachment seems to have caused it. Trying again with the same attachment. 'Start Upload' has been pressed and Status is 100%
Active Member
September 21, 2023
Offline
Finally succeeded in uploading the attachment. As described, remove data from one of the periods, e.g. the period for August 2020 for actuals, and there will be a gap in the line in the chart. Can this be solved by improving the measure?
Moderators
January 31, 2022
Offline
If you goal is to create a measure to take periods with no data into account, perhaps someone else can look into that. If, on the other hand you need a practical solution for a real life situation, I suggest you do the following:
1) Enter zero for all periods forecasts and budgets that you expect no business. That would make perfect sense as you would budget zero if you know that there will be no business in a certain period. Now the RT measures for Forecast and budget will work properly. No adjustment needed.
2) Use Power query to find Actual periods with no data and append a row for each missing period with zero.
See attached.
1 Guest(s)
