3 Replies Latest reply on Jan 8, 2019 10:38 PM by Shinichiro Murakami

Run Rate Calc

I need a calculation to work out the run rate so I can get a visual like below. This shows the cumulative revenue by week. We have the actual revenue from week 44 and future weeks 45-52 are based on the weekly average revenue.

Currently what I have is a calc that it

RUNNING_SUM(
IF ISNULL(
LOOKUP(
SUM(IF YEAR([Date]) = 2018 THEN [Revenue] END)
,0)
) THEN

([Revenue2018]/DATEDIFF('week',#1/1/2018#,TODAY(),'Monday')

ELSE
SUM(IF YEAR([Date]) = 2018 THEN [Revenue] END)
END
)

The problem with this is that we have some days with \$0 revenue and it imputes the average for those days. Whereas I only want to impute the average for the future days. Is there a way to go about this?

I would also like to have a different colour for the forecasted weeks if possible.

Attached file to work on.

• 1. Re: Run Rate Calc

That'll be bit tricky.

Seamless line is only available with over wrapping two lines.

So  used measure value with 2018 revinue (up to current week) and whole year line (WW01~WW53)

Thanks,

Shin

• 2. Re: Run Rate Calc

Shinichiro Murakami it doesn't seem to work out for me. Could you please have a look at this for me. Attached is where I got up to...

• 3. Re: Run Rate Calc

Your data set is Completely different from original post.

original

Could you close this thread with marking my answer as correct, and post another thread with clear problem statement and expected result.

Thanks

Shin