Inconsistent Calculated Column Values Despite Identical Comparison Values

[Oliver_Buchanan]

Hi Power BI Community,

I'm encountering an issue with a calculated column in my Power BI report that's yielding inconsistent results despite what appears to be identical conditions for the calculation. I would greatly appreciate any insights or assistance you can provide.

Issue Summary:

I have a calculated column named "Istoday" with the formula:

Istoday = IF([ETA Date] = [NZ Diagnostic Date], 0, 1)

Both "ETA Date" and "NZ Diagnostic Date" are supposed to be DATE only values. However, despite some rows having identical values for "ETA Date" and "NZ Diagnostic Date", the "Istoday" column returns different results (0 in some cases, 1 in others).

Additional Context:

• "ETA Date" is a calculated column based on another column "Service Cut off" with added days depending on the "Service Id".
• "NZ Diagnostic Date" is calculated with a DAX formula involving CALCULATE(MAX()) across the dataset, supposedly returning only date values. Yet, when used in visualizations, it shows a time component (12:00:00 AM) even though it's expected to be date-only.

Troubleshooting Steps Taken:

1. Verified that both "ETA Date" and "NZ Diagnostic Date" are in the date format and attempted to explicitly convert them to date values using DATEVALUE(), but the issue persists.
2. Checked for any hidden time components by converting dates to text and number but found no discrepancies.
3. Tried moving the calculation for "ETA Date" into Power Query to see if preprocessing the data would resolve the inconsistency.

Questions:

1. Why would the calculated column "Istoday" return different results for rows with seemingly identical values for "ETA Date" and "NZ Diagnostic Date"?
2. Could the issue be related to the way Power BI handles date and time data types or perhaps a hidden detail in the data transformation process?
3. Are there any recommended approaches to ensure consistent comparison of date values in calculated columns or measures?

Thank you in advance for your help! Any guidance on how to address these inconsistencies or suggestions for alternative approaches would be highly valuable.

[lbendlin]

Istoday = IF(DATEVALUE([ETA Date]) = DATEVALUE([NZ Diagnostic Date]), 0, 1)

Hint:  There is no "Date" or "Time"  or "Duration"  column type in VertiPaq. It's always DateTime  behind the scenes.

[Oliver_Buchanan · Accepted solution]

Hey thanks for the tip! After considering this i altered the calculation to utilise DATEDIFF instead as that should explicity only compare the date parts together and its now behaving as expected!

Istoday = IF(DATEDIFF([ETA Date], [NZ Diagnostic Date], DAY) = 0, 0, 1)