Forum Discussion

prerak's avatar
prerak
Advocate I
9 years ago

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

 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)

Need Help

1 Reply

  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    Community Support
    9 years ago

    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