7 Replies Latest reply on Mar 6, 2017 1:45 PM by Peter Haenggi

    Calculating MTD Through Previous Saturday

    Peter Haenggi

      Hey Guys,

       

      Got a question I was hoping someone could answer on a MTD calculation. I am trying to calculate MTD based on the previous Saturday. For example, let's say today is 02/28/2017 I want to be able to calculate through last Saturday a MTD for February - so only MTD of the 1st through the 25th of February.  I can't seem to quite get the formula to work right for me.

       

      For example a formula such as this:

       

      IF [BUSINESS_DATE]<=Today() AND

      [BUSINESS_DATE] = datetrunc('week',[BUSINESS_DATE]-1,"Saturday")

      AND DATEDIFF('month',[BUSINESS_DATE],Today())=0

      THEN ([REVENUE])

      END

       

      Thanks!!