Redshift Direct Query - Datediff returns negative for a positive delta between two datetime

[prerak]

 This image shows an issue with datediff(). For some reason it returns me negative numbers.

 This image, which is part ofthe same table, shows me accurate values.

Why would it work on some pairs of dates and not on others?

The Calculated Column is defined in DAX as

MC_HistDelta = IF(facts_events_usage_distribution[app_load] < facts_events_usage_distribution[hist_create], DATEDIFF(facts_events_usage_distribution[app_load], facts_events_usage_distribution[hist_create], DAY), 0)

[v-qiuyu-msft]

Hi @prerak,

Based on my test, the DAX of Calculated Column is correct. In your scenario, please check below points:

1. Verify both [app_load] and [hist_create] are Date/Time data type.

2. Make sure calculated column "MC_HistDelta" is defined in the table facts_events_usage_distribution.

3. Re-drag a table visual into the report, and put [app_load] and [hist_create] fields and calculated column "MC_HistDelta"

4. Try to clear cache data.

5. Try to update the Power BI Desktop to the newest version: 2.40.4554.463.

Best Regards,
Qiuyun Yu