cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ThomasBachmann Frequent Visitor
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     
ProductProduct IDTimestampPriceAmountTotal
Taco10009705/02/2019 10.33           20,00 kr.3           60,00 kr.
Burrito10009805/02/2019 10.45         100,00 kr.1         100,00 kr.
Salad10009905/02/2019 10.55           95,00 kr.2         190,00 kr.

 

FOOD COST   
ProductProduct IDCost CaluclationFood Cost
Taco10009701/02/2019           20,00 kr.
Burrito10009801/02/2019         100,00 kr.
Salad10009901/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
Super User

Re: Matching food cost with sales

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

 

1 REPLY 1
Highlighted
Super User
Super User

Re: Matching food cost with sales

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