1 Reply Latest reply on Nov 28, 2018 6:56 PM by Shinichiro Murakami

    Run Rate Calc

    Elie Rahi

      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.