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?
Hi David,
Can you please provide a sample file that replicates this problem?
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
There is no attachment. Please try again and don't forget to press "Start upload" before you submit your reply.
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%
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?
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.
