Incorrect Data being pulled from Excel Spreadsheet in SharePoint

[Anonymous]

I have an Excel Speadsheet located on Sharepoint which feeds into a Power BI Query. There are two columns containing dates, and a third column which holds a formula calculating the percentage of time between the first and second date that has passed, relative to today. On Excel the third column shows as 16%, but on Power BI the third column shows as 13.4%. I can't figure out why the values don't match.

[v-yuezhe-msft · Accepted solution]

@Anonymous,

Do you firstly connect to the Excel located on SharePoint Online in a new Excel file using Power Query option and then create custom column to calcultate the percentage in Power Query?


After that you import the Excel file that contains the custom column  to Power BI?  If so, I can't reproduce your issue. Could you please share your formula? Also why not directly connect to the Excel located in Sharepoint in Power BI Desktop and then create the percentage column?

Regards,
Lydia

[Anonymous]

Lydia,

I still can't figure out why there's a discrepency between the column values in the Excel on Sharepoint and the column values in Power BI. However, to get around the issue, I've created a calculated column in Power BI using the formula from Excel (modified for Power BI syntax). Thanks for your help!

[Anonymous]

Hi Lydia,

All three columns (Both dates, and the calculated percentage) are located on the Sharepoint Excel File. The value from the Excel file is diffrerent from the value on the Power BI Query. I don't use a caluculated column. The formula in Excel is 

=IF([@[First Date]]<TODAY(),100%,IF([@[First Date]]>TODAY(),0%,(100%/(DAYS([@[Second Date]],[@[First Date]])))*(DAYS(TODAY(),[@[First Date]]))))

The value shows at 17% on Excel, and is updated everyday. But on Power BI the value is stuck at 13.4%.