1 Reply Latest reply on Nov 1, 2018 7:48 PM by swaroop.gantela

    Last Day of the Last Month

    Tapiwa Tapiwa

      I want to show Revenue on the last day of the previous month and the prior month and same month last year.  For example today is November 1st 2018 I need numbers for October 31st 2018, September 30th 2018 and October 31st 2017.

      I have use the following,

      I created a Date field and for last month numbers

      DATEDIFF('month',DATETRUNC('month',[Initial Received Date]),{MAX(DATETRUNC('month',[Initial Received Date]))})=1

       

      This worked for the first month I had correct numbers but for the second month it did not. I have so many measures in the dashboard so I am using this calculated date,   an example is Prior Month End Revenue

      SUM(IF [Prior MTD]=TRUE THEN ([Revenue])  ELSE 0 END)

       

      Then I tried DATEADD('day',-1,DATEADD('month',1,DATETRUNC('month',[Initial Received Date])))

      When I use this  all the columns with this date break with the error; Can't compare datetime with boolean values

      How do I create the calculated Date that I can use with different measures to calculate last month and the month before numbers

        • 1. Re: Last Day of the Last Month
          swaroop.gantela

          I think you are on the right track.

           

          I tried these as LODs so that it would be clearer to see on the below table.

          I used a TestDate of 2017-01-01

          LastDayPrevMonth:

          { FIXED :SUM(

          IF [Order Date]=DATEADD('day',-1,DATETRUNC('month',[TestDate]))

          THEN [Sales] END)}

           

          LastDay2MonthsPrev

          { FIXED :SUM(

          IF [Order Date]=DATEADD('day',-1,

          DATEADD('month',-1,DATETRUNC('month',[TestDate])))

          THEN [Sales] END)}

           

          LastDayPrevMonPrevYear

          { FIXED :SUM(

          IF [Order Date]=DATEADD('year',-1,

          DATEADD('day',-1,

          DATETRUNC('month',[TestDate])))

          THEN [Sales]

          END)}

           

          Please see workbook v10.3 attached in the Forum Thread:

          Last Day of the Last Month

           

          286567date.png