Dax, Sales and Purchase price finding issue and proper sum up

[hansolu]

Hi

I do have an example Data set for a Burger Shop, I used this example already last year for another set of questions regarding Stock levels.
Now its a new request for pricing.

Each Burger has some Sauces, each Sauce some raw material.
For different shops I do have different sales of the burger.

I also have the sales price of each burger including a forecast (no sales price in the table) where I grab the last price of each burger and use this a potential salesprice

I also have Purchase Cost for each Sauce from different dates.

1. Questions: What is the Delta (Salesturnover - PurchaseTurnover) for each month?
Sheet "DM PVT", Measure "Purchase Turnover" and "Purchase Price for Calc"
As the Purchase Date might not match the sales date, I grab the last Purchase Date before the salesmonth and us this Cost to calculate the Purchase Turnover.
This works as long as my Pivot table is drilled down to the Sauce (Sub1Item) level, if I collapes Sub1Item, the Cost is wrong and therefore the Purchase Turnover is wrong


2. Question: Compared to a Reference Date, how did the Sales Price change and how much the Sales Turnover?
Sheet "PVT Reference Price" and Measure "Delta Sales Price Change" as well as Sheet "PVT Reference Turnover", Measure "Delta Sales Turnover vs Ref Date"
I grab the Sales Price at the Reference Date and compare the current (or last) sales price and calculate the difference. By having the Measure "Delta Sales Price Change" I can judge and write " no change, increase, decrease" already.
Thats fine I think, but can I use this to create a measure which is able to be filtered. Like a measure {"no change", "price increase", "price decrease"} and then put this measure into the filter section of the Pivot?

3 Question: Compared to a Reference Date, how did the Purchase Turnover change?
Not yet done, but I guess similar to 2.Question and not so difficult


I would need help to fix my issues in the first 2 questions

Thanks a lot