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

[saqibahmad]

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
C	D
Start Date	End Date	Span	Duration
24/03/2018 4:43:00 AM	24/03/2018 4:51:00 AM	0	8.000000026
24/03/2018 6:18:00 AM	24/03/2018 6:26:00 AM	0	7.999999984
24/03/2018 7:13:00 AM	24/03/2018 7:23:00 AM	0	9.999999991
24/03/2018 7:23:00 AM	24/03/2018 7:48:00 AM	0	25.00000002
24/03/2018 7:48:00 AM	24/03/2018 8:42:00 AM	0	53.99999997
24/03/2018 10:30:00 AM	24/03/2018 3:32:00 PM	0	302

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

[Phil_Seamark]

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]

@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]

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

[saqibahmad]

If apply formating then results end up as rounded which is not what i  want.

values before converting to milli seconds dont event match with the results in Excel.

Now i have moved onto reporting and further calculation and the end results shows that there are much difference in Poweer BI reports vs Excel outcomes.

I think either of the system is applying some formating unwantedly or am i using incorrect functions?

[v-piga-msft]

Hi @saqibahmad,

It seems that a problem with decimal point accuracy for Date type in Power BI.

By my tests with calculating the decimal number in Power BI and Excel, the results are the same.

 

Actually, we'd better use the dax formula mentioned by @Phil_Seamark to calculate the Date difference in Power BI.

If you really want to use the result calculated in Excel, as a workaround, you could calculated the date difference in Excel first then import the data to Power BI.

Best Regards,

Cherry