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

    Level of Detail (LOD) Calculation Using Dates


      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:








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



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

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

      THEN [Revenue]



      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




          and for 2 dates back


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




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


          Hope that helps