I receive daily excel outputs from a db containing customer order details. The daily excel sheet contains a snapshot of the remaining product value to be shipped to each customer but doesn't identify the change. I want to represent this within power bi, showing the start figure on day 1, the figure on day 2 and the variance. Because I receive the data in a separate table each day, I want to somehow combine and detail the differences. Example below shows the start values to ship on day, then the current value on day (on a different sheet/table), showing that we shipped 3000 of product 1, nothing for product 2, and 500 for product 3. I want to represent this in a table/matrix in BI, the customer, day1 value, day2 value, variation change.
Day1 (on one spreadsheet)
Customer Product 1 Product 2 Product 3
Abc customer 5000 1000 1000
Day 2 (on different sheet with identical columns)
Customer Product 1 Product 2 Product 3
Abc customer 2000 1000 500
Output Required
Customer Day 1 Product 1 Day 2 Product 1 Variance Product 1 Day 1 Product 2 Day 2 Product 2 Variance Prod 2 Abc customer 5000 2000 -3000 1000 1000 0
