1 Reply Latest reply on Sep 3, 2018 3:09 AM by Simon Runc

    Level of Detail (LOD) Calculation Using Dates

    andru.vo

      Hi Everyone,

       

      I am having trouble creating a Level of Detail calculation to dynamically compare sales revenue using dates (e.g. 8/2/2018). Some months, there can be sales revenue data from up to 3 different dates.

       

      I want to be able to compare the sales revenue from the latest date to any of the dates from prior sales data.

       

      The sales data are as follow:

       

      8/2/2018

      6/25/2018

      6/15/2018

      6/1/2018

      5/15/2018

       

      The calculation that I am using (but is not working) to compare sales data of 8/2/2018 and 6/25/2018 is:

       

      IF

      DATEDIFF('day',DATETRUNC('day',[Sales Date]),

      {Max(DATETRUNC('day',[Sales Date]))})=1

      THEN [Revenue]

      END

       

      Workbook is attached. Appreciate your help.

        • 1. Re: Level of Detail (LOD) Calculation Using Dates
          Simon Runc

          hi Andru,

           

          So the problem with using an LoD here is that the DATEDIFF is relative to the row you are in, and so not going 1 "partition" back (but one day back)....where it can't find any data

           

           

          The easiest way to do this is to use LOOKUP (a Table Calculation), although you will need the [Sales Date] in the vizLoD

           

          The formulas then become (the IF part is so it only returns the value to the last date)

           

          [Sales from 1 Date Ago - TC]

          IF MIN([Sales Date])=MAX({MAX([Sales Date])}) THEN

          LOOKUP(SUM([Revenue]),-1)

          END

           

          and for 2 dates back

           

          IF MIN([Sales Date])=MAX({MAX([Sales Date])}) THEN

          LOOKUP(SUM([Revenue]),-2)

          END

           

          We could do this with LoDs, but Table Calcs are simpler.

           

          Hope that helps