cancel
Showing results for
Did you mean:
Frequent Visitor

## Matching food cost with sales

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User

## Re: Matching food cost with sales

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] )
)
)

Highlighted
Super User

## Re: Matching food cost with sales

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] )
)
)