Problem with comparing DateDiffDays results

[laurma]

Hi!

On a timeline (sprint burndown chart), I’m trying to draw a vertical line through today’s date. Seems simple enough - determine the date and plot a point. So I started with Measures > Calculated members > isToday

DateDiffDays([Time].CurrentHierarchyMember.StartDate, Now())

Good! It shows 0 for today, 1 for yesterday, -1 for tomorrow etc. Working as expected.
Now, let’s wrap it:
CASE WHEN DateDiffDays([Time].CurrentHierarchyMember.StartDate, Now()) = 0 THEN Val(1) END

(The Val value is irrelevant, we just want everything else except today be null).
And yet it doesn’t work - the result of the comparison is empty.
So I created a second calculated member with just the comparison:

DateDiffDays([Time].CurrentHierarchyMember.StartDate, Now()) = 0

The expected result is ‘false’ for all days except today where the value should be ‘true’. Instead, all rows show ‘false’, even that where the isToday value is 0.

What am I doing wrong?
Thanks!

[GvidoN]

Hi Laurma,

The reason for this is that now() function returns also the time. When used in DateDiffDays() function it turns the day count in a decimal number. That is why it does not match 0.

To fix this I would recommend using “today” instead of now().

DateDiffDays([Time].CurrentHierarchyMember.StartDate, "today")

Hope that helps.
Gvido Neilands, flex.bi

[laurma]

I can’t believe it was that simple! Thank you GvidoN, much appreciated and it works beautifully