Forum Discussion

saqibahmad's avatar
saqibahmad
Frequent Visitor
8 years ago

Problem with Decimal Values in DAX, Excel(Precisely Correct ) vs Power BI (Incorrect)

Hi ,

 

Following is an example dataset from EXCEL and Power BI. Look at Duration column.... values doesnt match between both. I have tried changing formating Decimals, text, general in PowerBi to find exact match but no avail. Any thoughts?

Formula:

Duration  = (D-C)*60*24)

Excel   
 CD  
Start DateEnd DateSpanDuration
24/03/2018 4:43:00 AM24/03/2018 4:51:00 AM08.000000026
24/03/2018 6:18:00 AM24/03/2018 6:26:00 AM07.999999984
24/03/2018 7:13:00 AM24/03/2018 7:23:00 AM09.999999991
24/03/2018 7:23:00 AM24/03/2018 7:48:00 AM025.00000002
24/03/2018 7:48:00 AM24/03/2018 8:42:00 AM053.99999997
24/03/2018 10:30:00 AM24/03/2018 3:32:00 PM0302

 

 

 

PowerBI   
START_DATEEND_DATE Duration
24/03/2018 4:43:00 AM24/03/2018 4:51:00 AM 7.999999995
24/03/2018 6:18:00 AM24/03/2018 6:26:00 AM 8.000000005
24/03/2018 7:13:00 AM24/03/2018 7:23:00 AM 10.0000000011642
24/03/2018 7:23:00 AM24/03/2018 7:48:00 AM 24.9999999976717
24/03/2018 7:48:00 AM24/03/2018 8:42:00 AM 54.00000001
24/03/2018 10:30:00 AM24/03/2018 3:32:00 PM 301.999999999534

 

 

5 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    8 years ago

    Hi saqibahmad

     

    Have you looked at the DATEDIFF function?

     

    Column = 
    DATEDIFF(
        'Table1'[Start Date],
        'Table1'[End Date],
        MINUTE
        )

    You can choose from a number of different multiples

     

     

    • saqibahmad's avatar
      saqibahmad
      Frequent Visitor
      8 years ago

      phil

       

      Thanks, I had tried your solution but original problem still persists i.e duration values are still incorrecet when compared to Excel outcome.

       

       

      • Phil_Seamark's avatar
        Phil_Seamark
        Microsoft Employee
        8 years ago

        Are there millisecond values that aren't being displayed due to formatting?