2 Replies Latest reply: Sep 24, 2018 3:52 AM by Ugur Arslan RSS

    Current month, prior month

    Ugur Arslan

      Dear all,

       

      a very simple question where I struggle already for a few days.

       

      For the current invoice month I use [Invoice Year Month]={'$Current_Month_YYYYMM)'}.

       

      Question, how can I get prior month or two months prior?

       

      I tried [Invoice Year Month]={'$Current_Month_YYYYMM)',-2}. but it doesn't work.

       

      Please help me.

       

      Ugur

        • Re: Current month, prior month
          Justin Dallas

          Hello Yogurt,

           

          Just a few questions, is [Invoice Year Month] in the format of "YYYY MM" as in "2016 08"?  If so, you would have to convert it to a date:

           

          Date#([Invoice Year Month], 'YYYY MMM')

           

          Then take that date and subtract two months

           

          AddMonths(Date#([Invoice Year Month], 'YYYY MMM'), -2 )


          Then, convert that to your format


          Date(AddMonths(Date#([Invoice Year Month], 'YYYY MM'), -2 ), 'YYYY MM')
          


          So you should get something like this


          COUNT({<[Invoice Year Month]={"$(=Date(AddMonths(Date#([Invoice Year Month], 'YYYY MMM'), -1 ), 'YYYY MM'))"}>} [Invoice Id])
          


          I'm not sure about the set analysis syntax though.  Hope this helps.