Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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]))
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.
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).
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?
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]))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
98 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |