I want to calculate a new monthly sales target based on the sales achieved year to date vs the annual target, divided by the number of months left in the year.
My targets are in a separate table to my sales.
Targets table name: Targetsales
Sales table name: Orders
I have successfully created a measure for the Year To Date Target by using the following formula to multiple the monthly sales target by the number of months passed (based on today's date):
SALES TARGET YTD = Targetsales[TARGET_VALUE_MONTHLY] * MONTH(TODAY())
I have also created a measure to calculate the difference to target year to date, using the following:
YTD TARGET DIFF = CALCULATE(SUM(Orders[SALES]) - CALCULATE(SUM(Targetsales[SALES TARGET YTD])))
I now want to calculate the new monthly sales target, based on the YTD TARGET DIFF divided by the number of months remaining. I'm not sure whether to reference the other measures in the formula to achieve this, or if I can do it in one formula - for example:
NEW MONTHLY TARGET = Targetsales[YTD TARGET DIFF] - months remaining in current year
NEW MONTHLY TARGET = CALCULATE(SUM(Orders[SALES]) - CALCULATE(SUM(Targetsales[SALES TARGET YTD])) / months remaining in current year)
Please could someone help?
p.s. I'm new to measures and columns and don't know if there are limitations with using multiple measures or not.