1 Reply Latest reply on Jul 18, 2017 12:01 PM by Michael Gillespie

    MTD for last year

    mariam.ah

      Hi everyone,

      i calculate MTD  like that :

      MTD : IF [start Date] <= [parametre date ] AND DATEDIFF('month',[start Date ],[parametre date ])= 0 THEN [sales] END it works

      but i want to calculate MTD for last year , i don't know how

      Any help

        • 1. Re: MTD for last year
          Michael Gillespie

          Mariam, this shouldn't be too tough.

           

          You need to create a new calculated field for the previous year using the same formula as your current year, but with one additional test.

           

          IF YEAR([Start Date])=YEAR([Start Date])-1 //this pulls in any records for the previous year

          AND YEAR([Start Date])-1<=[parameter date]-1 //this does your comparison but based on the PREVIOUS year, not the CURRENT year

          AND DATEDIFF('month',[start Date ],[parametre date ])= 0

          THEN [sales]

          END

           

          I can't test this to be sure that it will work (it depends on a bunch of assumptions) but that's the general pattern you want to follow.