6 Replies Latest reply on Jun 11, 2018 1:12 PM by Samanth Yadav

    YTD Calculation

    Samanth Yadav

      Hi All,

       

      I'm stuck with YTD calculation. Attached is the excel file which has fields Loaddate, Date(a Parameter), Dollar, Volume, Dollar/Volume (Calculated in excel) and YTD (Calculated in excel).

       

      My Calculation for YTD:

      sum(if DATEPART('month',[LOADDT])<=datepart('month',[Date])

      and DATEPART('year',[LOADDT])=datepart('year',[Date])

      and CONTAINS([Metric],"Dollar") then [Value] end)/sum(if DATEPART('month',[LOADDT])<=datepart('month',[Date])

      and DATEPART('year',[LOADDT])=datepart('year',[Date])

      and CONTAINS([Metric],"Volume") then [Value] end)

       

      This holds good for January. But when I select February it sums up the JAN and FEB dollar & volume and then gives the result.

      For example, when I select Jan 17 the Dollar/Volume is 20 and when I select Feb 17  the YTD comes up as15 as per my calculation which is the not the result I'm looking for. I need the result to be JAN-dollar/vol + FEB-dollar/vol

      (i.e. 20+13.33 = 33.33) similarly, when i select Mar 17 I need the result to be JAN-dollar/vol + FEB-dollar/vol + + Mar-dollar/vol(i.e. 20+13.33+12 = 45.33) .

       

      Can someone please help me out with the right calculation.

       

      Regards,

      Sam