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:

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

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