Inaccurate Data between Quickbooks Online and PowerBI Data Set

[afvijay]

We recently decided to try out PowerBI and linked our QBO account with Power BI.

When we got our data, our balance sheet data on banks are way off compared to the QBO data. When we ran a Balance Sheet report in QBO and compared to PowerBI data, they both are off.

Any idea or suggestion, why the discrepancy in data? Its not because of time difference, but data seems to act weird and not sure what we should be looking for as the refresh was successful with no errors.

Appreciate any help or pointers.

[v-xicai]

Hi @afvijay ,

Could you please show us the differences between the QuickBooks Online and Power BI Dataset in screenshots? Do mask sensitive data before uploading.

You may refer to the link to learn how to connect to QuickBooks Online: https://docs.microsoft.com/en-us/power-bi/service-connect-to-quickbooks-online.

Best Regards,

Amy

[afvijay]

Thanks for the kind reply. Please refer to the below for screenshots. 

• We made sure the accounts are all in RECONCILED status to ensure our GL data reflects the latest figures.
• These are all bank accounts
• GL shows the data right no QBO reports and GL registers.
• PowerBI is throwing numbers that doesn't make any sense.

Screenshot of side by side compare : https://ibb.co/5K1fp9z 

Thank you

[afvijay]

After doing a little digging on one account that had a $3,000 GL balance on PowerBI Dataset and compared it line by line to QBO transactions we found there the PowerBI dataset is NOT calculating the GL Ledger data due to currency conversions.

1. We had one Canadian Invoice for $11,300 and we got paid $11,300 in Canadian dollars.
2. The check got deposited in the US bank account
3. When the bank processed the check, it deposited ROUGHLY $8,000.
4. The transaction was reconciled with ending balances matching both on A/R and bank.

So in this scenario, the invoice has been paid fully and the A/R is zero, the bank converted and deposited the correct USD amount.

However PowerBI somehow calculates the difference between the CAD invoiced amount and USD as the correct GL balance. 

• In QBO the current bank GL balance = 0
• In PowerBI data set the current bank balance is $3300.

So if I am calculating CASH on hand scenario, right now PowerBI shows we got $3300 at hand which is wrong. Is there a different way to look at this ??