1 Reply Latest reply on Mar 27, 2017 2:11 PM by Patrick Van Der Hyde

    Same YTD value as month value for any period

    Prakash A



      I have 3 metrics - Sales, Revenue and Month end rate. I present them as monthly values and YTD values. I am able to present them correctly except the Month end rate. My month end rate is a point in time calculation, so my YTD value has to be the same as month end value. For e.g. If the current month is Mar 2017, then for month end rate the value should be the value at end of Mar 2017, and not adding up from Jan-Mar 2017. But the other 2 metrics - sales and revenue has to be added up for YTD calculation.


      I have created a parameter for selecting the month, and based on that I am using below calculated field.


      For showing monthly value = SUM(IF MONTH([Select Period]) = MONTH([Date]) AND YEAR([Select Period]) = YEAR([Date]) THEN [Actual] END)


      For showing yearly value = SUM(IF YEAR([Select Period]) = YEAR([Date]) AND MONTH([Date]) <= MONTH([Select Period]) THEN [Actual] END)


      [Actual] is my measure with values for Actual numbers. Similarly, I have another measure for Budget numbers as [Budget].




        • 1. Re: Same YTD value as month value for any period
          Patrick Van Der Hyde



          Hello,  is this not working for you?  My thought is to do this is to use datetrunc() to truncate the first day of the month to the first day of the selected month and avoid that Month(Date) and Year(Date) comparisons that you have going on .    If you have a small sample workbook to share and can let us know of the problem encountered, this looks like a quick fix.