Forum Discussion
mbudiman
Helper III
Helper IIICalculate difference between versions of Forecast data
Hello, I need help for a DAX measure to calculate Forecast Qty difference between versions for each Fiscal Qtr. A Fiscal Qtr may have multiple versions of Forecast. See example data below. ...
and try this
Measure = VAR NumQuarter = SELECTEDVALUE('Sales Forecast'[NumQuarter])-1 VAR PQTY = CALCULATE([FCST_QTY], 'Sales Forecast'[NumQuarter]= NumQuarter , REMOVEFILTERS('DimVersionNo'),REMOVEFILTERS(DimFYFISCAL)) RETURN [FCST_QTY] - PQTY
kushanNa
Super User
Super Usermbudiman oh okay , please check if this dax works , this is a dynamic version one
FCST Qty Diff Dynamic =
VAR MaxVersion =
CALCULATE(
MAX('Forecast'[Version No]),
ALLEXCEPT(
'Forecast',
'Forecast'[SALES_CHANNEL],
'Forecast'[SALES_REGION],
'Forecast'[SALES_COUNTRY],
'Forecast'[PBU],
'Forecast'[PRODUCT_RANGE],
'Forecast'[SUB_MAIN_CAT_CODE],
'Forecast'[Part No],
'Forecast'[FY_FISCAL_QTR]
)
)
VAR PrevVersion =
CALCULATE(
MAX('Forecast'[Version No]),
ALLEXCEPT(
'Forecast',
'Forecast'[SALES_CHANNEL],
'Forecast'[SALES_REGION],
'Forecast'[SALES_COUNTRY],
'Forecast'[PBU],
'Forecast'[PRODUCT_RANGE],
'Forecast'[SUB_MAIN_CAT_CODE],
'Forecast'[Part No],
'Forecast'[FY_FISCAL_QTR]
),
'Forecast'[Version No] < MaxVersion
)
VAR MaxQty =
CALCULATE(
SUM('Forecast'[FCST Qty]),
'Forecast'[Version No] = MaxVersion
)
VAR PrevQty =
CALCULATE(
SUM('Forecast'[FCST Qty]),
'Forecast'[Version No] = PrevVersion
)
RETURN
MaxQty - PrevQty
