Capturing Dynamic Week-over-Week Sales Changes at Line Item Level

[Vmonc]

I'm working on creating a report in Power BI that aims to provide a week-over-week comparison of sales values at the sales order-line item level.

My initial approach was to use DAX to compare the current week's sales with the previous week's sales. However, I've encountered an issue where any changes made to a line item in the current week are captured, but the sales details from the previous week would not be visible in the report.

To simplify, let's consider an example: Last week, a sales order with 5 line items resulted in a total of $1 million. This week, one of the line items, valued at $250,000, was rejected. When I generate the report this week, I would only see the remaining amount, which is $750,000 for 4 line items.

I want to track week-over-week changes at the line item level, including any updates or rejections.

Should I consider using snapshots or another approach to achieve this? I Would aprreciate if anyone could offer insights or suggestions regarding this.

[lbendlin · Accepted solution]

Power BI has no memory. Your data must be stored further upstream.

You could consider using snapshots but that would create larger storage demands.

The better solution is to use CDC or other forms of event tracking.  In your example you would have a change history table that would mark the event of a line item being rejected at a certain date (ideally also stating the reason).  Then you can recalculate the exact status of any order at any point in time, and easily create change charts (Sankey for example).