How to accurately measure sales in Power BI?

Shopify Plus

Topic summary

Power BI sales not matching Shopify due to how Shopify recognizes revenue, refunds, and shipping. Shopify posts refunds/returns on the date they occur (not the original order date), and shipping is stored at the order header level.

Proposed approach:

  • Split data into two models: (1) payments/refunds to align with Shopify revenue, and (2) product-level sales for items sold/returned.
  • Reconcile at a daily grain using a report by order and product to balance figures.
  • Include shipping from the order header in revenue calculations.

Current development:

  • The author adjusted logic to apply refunds on the refund date, which improved alignment.

Open challenges:

  • Cash refunds issued without product returns (not visible in order lines). Question: should payment transactions be included to capture these?
  • Exchanges and order edits: without adjustments, the replacement item (product Y) is attributed to the original order date. Shopify indicates these are summarized in the Sales Agreement endpoint, which may be needed to date exchanges correctly.

Status: No final resolution. Key questions remain on handling non-return refunds via payment transactions and using the Sales Agreement endpoint for exchanges/order edits.

Summarized with AI on December 19. AI used: gpt-5.
1

Hello,

I brought my shopify data into Azure so that I can pull it into Power BI. I’m now creating reports, but for some reason, my sales are not matching Shopify. Shopify measures sales in weird and mysterious ways. Has anyone been successful in creating a sales measure in Power BI that you’re confident in?

Thanks!

2

Hi - Yes

I have broken the data into 2 groups, 1 being the payments and refunds (which matches closest to the Shopify revenue) and the other being product sales.

On the payments side, shopify recognises the revenue on the day that it happened, ie you have an order from 2 weeks ago (or 2 months) where a refund is given or a stock item is returned, that is shown on shopify as having happend today. If you work everything at an ordered date level you will not balance. Shopify also has shipping on the order header level which needs to be taken into account

In power BI i also have a sales by product level which shows which products sold on the days as well as takes care of product returns

Best way of balancing is to get a sales report at the day level - by order by product and check your balancing with that

Thats how I have managed to get it working (there might be better ways)

3

Thanks. So, I did figure out that I needed to look at the refund date and subtract the refund from sales for that refund date and not the order date. I don’t understand the payments as well though. I’ve learned that my staff have managed to refund money back to the customer without actually returning any product. How do you handle that situation? At this point, I’m just looking at the order lines to calculate sales, so it wouldn’t pick those up, but do I also need to look at the payment transactions? Or, do you not have that issue?

The other thing I’m not sure about is the Exchanges and Order Edits. Shopify told me they are summarized in the Sales Agreement endpoint. Have you been using that? For example, if I exchange product X for product Y, I would see a refund come through on that date for product X, but product Y (a new sale) would be attributed back to the original order date unless I looked at this new sales agreement entity which calls out the date of the exchange.