Problem with comparing DateDiffDays results

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.

Screenshot_1

What am I doing wrong?
Thanks!

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.

20

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

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

Hope that helps.
Gvido Neilands, flex.bi

1 Like

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