Above Date Compare

[keithlawrence]

I have a need to put a chart my dashboard that looks at dates in the above row and compare it to information in the current row

This is a basic view of my data:

LOAD * INLINE [
ID, Name, Start Date, End Date
123, ABC DEF, 16/10/2017, 10/07/2018
234, ABC DEF, 13/04/2018,
124, GHJ KLM, 02/03/2018, 16/03/2018
237, GHJ KLM, 30/04/2018,
];

The first name has a start date which is before the end date of the prior row. I can get an expression to flag this row as being invalid but I need to flag both rows which is where I'm failing.

I'm using this formula:

If( Above( TOTAL [Name] ) = [Name],
      If( [Start Date] - Above( TOTAL [End Date] ) < 0,
             1,
             0  ),
     0 )

It flags my second row with a 1. I need a 1 to appear in BOTH rows so I can limit the table to anything over 0. I'm sure I need to use an aggr but I can't get the function to return any results. Even looked at RangeSum but again not getting what I need.

Anyone offer any advice?

[sunny_talwar · Accepted solution]

May be this

If(
	(Above(TOTAL [Name]) = [Name] and [Start Date] - Above(TOTAL [End Date]) < 0) or
	(Below(TOTAL [Name]) = [Name] and Below(TOTAL [Start Date]) - [End Date] < 0)
, 1, 0)

[keithlawrence]

How silly of me to not think of 'BELOW' too. That's worked for me, thanks Sunny!