4 Replies Latest reply on Jun 1, 2012 1:06 PM by Carol Gensimore

    Prorating Previous Year/Month Gross Profit

    Carol Gensimore

      I am comparing 2011 Gross Profit to 2012 on a monthly basis. In my overall analysis I would like to prorate the current month May 2011 Gross Profit to May 2012 Gross Profit by calculating the days currently expended in May 22/31 and multiplying it times the May 2011 Gross Profit.  I would like this to be dynamic, changing by day and holding all previous months at actual numbers.

       

      I created the formula below but have not been successful:

       

      if(MONTH( TODAY()  )= 5 And YEAR(#May 31, 2011#)= 2011 )then[GP$]*[Prorated Month to Day]else[GP$]end

       

      My extracted file is attached.

       

      Thank you.

        • 1. Re: Prorating Previous Year/Month Gross Profit
          Alex Kerin

          Your data is on a cube, so the tbwx above does not have it (The worksheet "Profit by Month" has not been opened because it is based on the data source "Gross Profit Cube (GrossProfitCube)", which could not be opened.)

           

          However, from your prior question, we know the total days in a month:

           

          datediff('day',datetrunc('month',[Date]),dateadd('month',1,datetrunc('month',[Date])))


          We can calculate the number of days into the month:

           

          datepart('day',today())

           

          so the ratio is datepart('day',today()) / datediff('day',datetrunc('month',[Date]),dateadd('month',1,datetrunc('month',[Date])))

           

          We can check if the current row is in the current month

           

          if datetrunc('month',today())=datetrunc('month',[Date]) then .....

           

          Now the problem lies in aggregation - I don't know how your data is structured (one row per month that gets updated daily, or multiple rows per month (per hour, day...) that we need to somehow roll up

          • 2. Re: Prorating Previous Year/Month Gross Profit
            Alex Kerin

            Note - this does assume that the data is added daily. If that isn't the case then we would need some other datestamp to get the update day.

             

            We may have to get complicated with table calcs....

            • 3. Re: Prorating Previous Year/Month Gross Profit
              Carol Gensimore

              Alex,

               

              Thank you very much for the quick follow up.  However, I have since been called upon to put out a fire.  I will get back to you next week.

               

              Carol

              • 4. Re: Prorating Previous Year/Month Gross Profit
                Carol Gensimore

                Alex,

                 

                Your formula's were great.  My biggest struggle was getting the "sale date" to be part of the formula.  When I accessed the cube through Analysis Services, I could not utilize the "sale date" ( I am assuming because of hierarchy issues).  When I connected to the cube directly through the sql server it worked. - Trial and Error.

                 

                Thank you very much. 

                 

                Carol