I’ve got a fun one. I have a Power BI dashboard that is supposed to show Order Backlog over time (as a running total). I have a top-level subquery that has 2 subqueries joined to a date table. One that totals up order price by Order Date and another that totals up the invoice dollars by Invoice Date. I look at the difference and do a running total on that difference. I’m sure I over-simplified it. Needless to say, my number as of today is way off of what I know the backlog to be simply by running a sum of all open orders. What other logic might I be overlooking for generating this “backlog over time” report?
We might need to see some more details.
Something is hitting my spidey senses, but it may jut be those beers I had at lunch.
Are all orders in backlog loaded in the same currency?
@Tomas and @klincecum - It ended up being sales kits. It was counting their sales dollars twice!! We figured it ou!
I realize I never really answered how I solved it. Just figured I’d add this closure in case future me can’t remember what happened.
I added a simple case statement in the calculated value for sales dollars that makes sure OrderDtl.KitFlag = OrderDtlKitPricing before outputting the value. In our case, all our sales kits are set to use component pricing (C) and the KitFlag field will tell you if the line is a kit parent (P) or a kit component (C). If the case of non sales kits, they will both be blank and equal.