I'm not sure, but I'm wondering if it might benefit from using a date scaffold:
This may help fill in the gaps.
Thanks for the suggestion but, so far, I have been unable to find a solution with scaffolding. The biggest issue is that scaffolding seems to work with a set range of dates, and I update this report on a daily basis. If there is a way to make date scaffolding automatically update to the current date, I have been unable to determine how.
If there is a way, or there are any other thoughts or suggestions on how to resolve this issue, please let me know.
My apologies, I answered after only taking a quick look into your workbook.
I'm wondering if it is a divide by zero situation that is causing the blanks.
I don't know if this will correct your issues, but I tried using a lookup to
get the previous day's denominator:
(RUNNING_SUM(SUM([Total Margin] ) ) /
( IF ISNULL(AVG([Mar Work Days to Date (HWY)]))
THEN LOOKUP(AVG([Mar Work Days to Date (HWY)]),-1)
ELSE AVG([Mar Work Days to Date (HWY)])
*[Mar Working Days]
It at least appeared to populate the table.
Please see workbook v2020.1 attached in the Forum Thread:
335316pace.twbx 1.7 MB
I don't think the scaffold will be needed in this case,
but in general, I think it is ok to create a scaffold that by
default goes far into the future, like 2030.
That is, unless you're dealing with a massive amount of data.
But in the end after the join to the scaffold, one can immediately
filter out all those dates that are in the future.
There are still a few missing items, but this did help for sure. Thank you.
Sorry for the stepwise approaches.
Now this is uncanny, but I just received the exact same request at work,
to calculate off of missing values.
This is the method I used for that problem, in this case I'm calculating the Avg Wk Days:
IF NOT(ISNULL(AVG([Mar Work Days to Date (HWY)]))) THEN AVG([Mar Work Days to Date (HWY)]) // if there is a value, return it
ELSEIF ISNULL(LOOKUP(AVG([Mar Work Days to Date (HWY)]),-1)) // if the previous avg was blank, lookup what this function previously returned
ELSE LOOKUP(AVG([Mar Work Days to Date (HWY)]),-1) // otherwise if there was a previous value, look it up
It seems to populate everything now, but I didn't verify the values.
Please see workbook attached in the Forum Thread.
335316pace2.twbx 1.7 MB