Matching food cost with sales

[ThomasBachmann]

We update our food / production cost every month based on the price of supply.

In one report our need to match the cost of a menu item with the appropriate food cost.

Example:

In January I sell 1 taco at $10 a piece, my food cost that month is $1, thereby I have a margin of $9.

In February I sell 1 taco at $10 a piece, my food cost that month is $2, thereby I have a margin of $8.

My data looks like this:

SALES
Product	Product ID	Timestamp	Price	Amount	Total
Taco	100097	05/02/2019 10.33	20,00 kr.	3	60,00 kr.
Burrito	100098	05/02/2019 10.45	100,00 kr.	1	100,00 kr.
Salad	100099	05/02/2019 10.55	95,00 kr.	2	190,00 kr.

FOOD COST
Product	Product ID	Cost Caluclation	Food Cost
Taco	100097	01/02/2019	20,00 kr.
Burrito	100098	01/02/2019	100,00 kr.
Salad	100099	01/02/2019	95,00 kr.

How do I match the sales with the food cost with the food cost from the appropriate month?

[AlB · Accepted solution]

Hi @ThomasBachmann

You can try this for a 'MonthPrice' calculated column in your table SALES:

MonthPrice =
CALCULATE (
    DISTINCT ( 'FOOD COST'[Food Cost] ),
    FILTER (
        'FOOD COST',
        'FOOD COST'[Product ID] = SALES[Product ID]
            && MONTH ( 'FOOD COST'[Cost Caluclation] ) = MONTH ( SALES[Timestamp] )
            && YEAR ( 'FOOD COST'[Cost Caluclation] ) = YEAR ( SALES[Timestamp] )
    )
)