cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
prerak Frequent Visitor
Frequent Visitor

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

Issue_PowerBI_DateDiff_Days.png

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

 

Issue_PowerBI_DateDiff_Days_Correct.png

 

 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)

1 REPLY 1
Moderator v-qiuyu-msft
Moderator

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

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.

 

q4.PNG

 

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

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.