4 Replies Latest reply on Sep 23, 2018 5:04 AM by Nancy Sidhu

    Dynamically change the latest months according to a fixed date, every month

    Nancy Sidhu

      Hey everyone!

       

      I'm working on creating a calculation which enables me to look at past 3 months' data.

       

      (DATETRUNC('month',[DATE])=DATETRUNC('month', DATEADD('month',-1,{[Max date]}))): For last month

      (DATETRUNC('month',[DATE])=DATETRUNC('month', DATEADD('month',-2,{[Max date]}))): For second last month

      (DATETRUNC('month',[DATE])=DATETRUNC('month', DATEADD('month',-3,{[Max date]}))): For third last month

       

      This is working fine!

       

      The issue is that I have to design a calculation based on following condition:

       

      If the date of current month is upto 7th then the last 3 months excluding the current month should be displayed. If the date of current month is 7 or crosses 7, then the data of last 3 months including the current month should be displayed.

       

      Example: Today is Sept 22,2018. So I want to see the data of Sept,Aug and July.

      If today was Sept 2,2018 then I would want to see the data of Aug,July and June.

       

      Any Suggestions?

       

      TIA!