3 Replies Latest reply: Oct 5, 2017 11:32 PM by William Walsh RSS

    Calculate the percentage of each Month Sales against Forecast

    William Walsh

      Hi,

       

      I am currently trying to calculate the percentage of spend for each month of the year against each other. The following table is my data set with a CurrentDateKey in a different table that defines the current month of the year that uses the same format as the DateKey.

            

      DateKeyStoreMonthSalesSales ForecastFinancialYear
      072017Store1July3253552017
      082017Store1August10202017
      092017Store1September1251352017

       

      The following code for my demension calculates the sales and forecast of all the months that have past cumulatively to define the percentage of sales against the forecast for the total year to date. Then defines the percentage of sales per store but just counting the stores for the related dual value.

       

      What I want to be able to do is calculate the percentage of sales against forecast for each month against each other.

       

      What the current code calculates =  (325+10+125) / (355+20+135) =  90.1% of expenditure against forecast

       

      How I want the code to calculate = (325/355) + (10/20) + (125/135) = 78% of expenditure against forecast

       

      Aggr(

       

      if(Sum({$<FinancialYear={'2017'} >}Sales)/Sum(if(DateKey >= '072017' and DateKey < (CurrentDateKey), Sales Forecast)) > 1.5, Dual('Greater than 150%',5),

       

      if(Sum({$<FinancialYear={'2017'} >}Sales)/Sum(if(DateKey >= '072017' and DateKey < (CurrentDateKey), Sales Forecast)) > 1.25, Dual('126% - 150%',4),

       

      if(Sum({$<FinancialYear={'2017'} >}Sales)/Sum(if(DateKey >= '072017' and DateKey < (CurrentDateKey), Sales Forecast))  >= .75, Dual('75% - 125%',3),

       

      if(Sum({$<FinancialYear={'2017'} >}Sales)/Sum(if(DateKey >= '072017' and DateKey < (CurrentDateKey), Sales Forecast))  >= .50, Dual('50% - 74%',2),

       

      if(Sum({$<FinancialYear={'2017'} >}Sales)/Sum(if(DateKey >= '072017' and DateKey < (CurrentDateKey), Sales Forecast))  < .50 , Dual('Below 50%',1),

       

      ))))),Store)

       

      Thanks,

      William