Hi Jmogielnicki, I think the attached workbook works. Instead of going after the date, I wrote calcs to test for SUM([amount])>0, which seems to produce the chart you want. I wrote two new calcs:
Actual (blue line):
Differential (grey bars):
IIF(SUM([Amount])>0,[Actuals minus Budget], NULL)
Here's the chart:
Hope this is what you were looking for.
Running Total Issue-sw.twbx.zip 28.8 KB
Hey Shawn, that is super helpful, and definitely fixed one of the two issues. Was still having trouble getting the running sum of the variance to cut itself off but fixed that with a calculation that referenced one of the calcs that you set up:
IIF(isnull([Actuals minus Budget]), NULL,
RUNNING_SUM(sum((if [Type] = "Actuals" then [Amount] else null end)))-
RUNNING_SUM(sum((if [Type] = "Budget" then [Amount] else null end))))
I'll attach the workbook in case anyone else comes around with a similar issue.
Thanks for the help.
This solution works well if [Amount] is not NULL, but when it is, the running sum will not display, causing discontinuities in the line. For example, if my line is showing cumulative increases by day, and no records are added on weekends, then the line is broken into segments.
I could roll up to by-week instead of by-day, but 'week 1', 'week 2', etc is not as desirable as actual dates.
I have the same problem, I have discontinuities in the lines. Any idea to solve it?
You can try this, Just replace "Actual".
IIF(SUM([Actual])>0,RUNNING_SUM(SUM([Actual])), (SUM([Actual])+ PREVIOUS_VALUE(0)))
In a similar case, i used the LAST function to stop showing the running sum after current date. Here goes my calculated field -
IF LAST()>=(DATEDIFF('week',TODAY(),[End Date],'Monday'))
1 of 1 people found this helpful
This might be too late for you, but maybe someone else has the same problem and finds it here. I found this article that solved it for me: