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.
I'm using DATEDIFF shown below in a calculated column to compare dates in two different columns to show me the difference in days. For some rows however, the dates are the same and instead of showing a 0 in the calculated column, I need it to show 1. In addition, in some cases there will be no dates in either column. Currently in these instance, no value is returned in the calculated column. I want this to remain this way and not for a 1 to be returned as they are the same.
Date1-Date2 days = DATEDIFF('Table'[Date1],'Table'[Date2],DAY)
Solved! Go to Solution.
Hey @TIGER8855 ,
provide sample data and the expected results.
Regards,
Tom
@TomMartens refer to table below:
Date1 | Date2 | Date1-Date2 days |
28-Jul-19 | 9-Dec-20 | 499 |
28-May-18 | ||
29-Jul-19 | 29-Jul-19 | 1 |
29-Oct-19 | ||
2-Oct-19 | 29-Oct-19 | 27 |
10-Oct-21 | 10-Oct-21 | 1 |
30-Nov-21 | 24-Jul-22 | 236 |
Okay, sorry for the reoccuring issue. Hard to do without the actual data, I am trying free hand here. Try this:
Date1-Date2 days =if( DATEDIFF('Table'[Date1],'Table'[Date2],DAY) = 0 ,1,if(DATEDIFF('Table'[Date1],'Table'[Date2],DAY) = Blank(),Blank(),DATEDIFF('Table'[Date1],'Table'[Date2],DAY)))
@PowerUserR no good unfortunately. It's back to adding a 1 when both dates are blank.
Hello,
Date1-Date2 days =
Var output = DATEDIFF('Table'[Date1], 'Table'[Date2], DAY)
Return
IF(output = 0, 1, output)
@ReneMoawad Blank Date1 & Date2 are returning a value of 1 with this one unfortunatley.
Try the below
Date1-Date2 days =
Var output = DATEDIFF('Table'[Date1], 'Table'[Date2], DAY)
Return
IF(
'Table'[Date1] = BLANK()
&& 'Table'[Date2] && BLANK()
, 0
, IF(output = 0, 1, output)
)
@ReneMoawad Thanks for your on-going help. The Blank Date1 & Date2 are still returning a value of 1 with this one. In a seperate post I have provided a table with sample data and the expected result for the Date1-Date2 days calculated column.
Hi,
Try this:
Date1-Date2 days =if( DATEDIFF('Table'[Date1],'Table'[Date2],DAY) = 0,1,DATEDIFF('Table'[Date1],'Table'[Date2],DAY))
@PowerUserR I tested and it changes the 0 to 1 for the same dates however it also now adds a 1 when both dates are blank. Is there any way to exclude a value showing when both dates are blank?
Hi, might not be the best code I have written, but try this:
Date1-Date2 days =if( DATEDIFF('Table'[Date1],'Table'[Date2],DAY) = 0 && DATEDIFF('Table'[Date1],'Table'[Date2],DAY) <> Blank() ,1,DATEDIFF('Table'[Date1],'Table'[Date2],DAY))
@PowerUserR with this code, blank dates now return no values however for instances where the date is the same, it's returning a 0
Thanks @PowerUserR ! This one works exactly as I expected. Thanks for your continued help to a solution.
Lesson for me next time to include sample data from the start.
Of course!
I think that will be helpful as you run into future issues.
Cheers!
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 |
---|---|
114 | |
99 | |
83 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |