1 2 Previous Next 15 Replies Latest reply on Jul 19, 2018 8:27 AM by Sean osean

    Latest Month, Prior Month, and Previous year same month with the corresponding month to display on visualisation

    Sean osean

      Hello Everyone,

      I have a requirement to compare the Latest month ended, Prior month and last yr same period month, % of year of year change, % of Month over month change. I can use this following calculated field to display current month and prior month as well as last yr month date by changing the "number".

      (IF DATEDIFF('month',DATETRUNC('month',[Order Date]),{MAX(DATETRUNC('month',[Order Date]))})=0 / 1  and for 12 (last yr)

      THEN [Sales]

      END) as Latest_month

      But this labels the field as Latest_month / Prior_month or whatever the naming convention we wish.

      My question is, how can i display the corresponding month dynamically, for example :-

      If current month is June 2018 it should display the same 2018 / May 2018 and June 2017 for last yr. Which should change on monthly bases as month passes. I do not want to display all the 12 months in-between this month and last yr.

       

      I tried this function in filter as well but again it will display all 12 months to make my function work. And i do have other calculations as well (% change YOY / MOM etc)

       

      DATEDIFF('month',

        DATETRUNC('month',[Order Date]),

        {MAX(DATETRUNC('month',[Order Date]))}) <= 12

       

      Thank you in Advance for your help and thoughts.

       

      Message was edited by: Sean osean. Attached is the sample data. Thank you

        1 2 Previous Next