Calculating Days Between a Date and Today

[Rsanjuan]

Hi,

I'm trying to calculate the number of days between 1 date field and today.

I created a column for Today = DATE(YEAR(NOW()),MONTH(NOW()),DAY(NOW()))

Then did this:  Remaining Days Until Due Date = ('Account  Planning'[Due Date]-'Account  Planning'[TodayCol])*1

The two due dates that I have is 6/23/17 and 5/30/17.  One is overdue.  It should return 4 and -20 respectively.

However, it is returning the values as 2092 and -10460?  Both fields are in date format.  Am I doing something wrong?

[srhoag]

I have a simple calculation to show how many days until a shipment of product is expected to arrive.   The formula I used is:  Expected Arrival in days = int('FA Live$Purchase Line'[Expected Receipt Date]-Today()).    It works fine EXCEPT for shipments arriving today.   Then it shows 3 days rather than 0 days.

I found that the INT(Today()) = 43039  (October 31,2017)

I found that the INT('FA Live$Purchase Line'[Expected Receipt Date]) for a shipment due today = 43042.

This is confusing to me.   Any help would be appreciated.

[Chihiro]

Try using...

=INT('Account  Planning'[Due Date]-'Account  Planning'[TodayCol])

Also, instead of using DATE() & NOW() for Today column. You can just use TODAY().

EDIT: If above doesn't fix it. Try first converting each of date column to integer using INT(COLUMN).

If the value does not fall in range of 42736 to 43100 for year 2017. Then there's something off with your date column(s).

[Rsanjuan]

@Chihiro

Thanks.  For some reason, it's not working for me.  There are some rows that have a "null" in the Due Date field.  Will that cause it?

[Chihiro · Accepted solution]

Yes, it will likely return negative INT value for today's date, if there are blanks.

You can nest the function in IF() function to return blank if [Due Date] is blank.

=IF(ISBLANK('Account  Planning'[Due Date]),BLANK(),INT('Account  Planning'[Due Date]-'Account  Planning'[TodayCol]))