Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

Reply
Oliver_Buchanan
Regular Visitor

Inconsistent Calculated Column Values Despite Identical Comparison Values

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.

Oliver_Buchanan_0-1709157154148.pngOliver_Buchanan_1-1709157214467.png

 



1 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)

 

View solution in original post

2 REPLIES 2
lbendlin
Super User
Super User

 

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.

 

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)

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.