Forum Discussion

mbudiman's avatar
mbudiman
Icon for Helper III rankHelper III
11 months ago
Solved

Calculate 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.   ...
Need Help
  • Ahmedx's avatar
    Ahmedx
    11 months ago

    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

mbudiman's avatar
mbudiman
Icon for Helper III rankHelper III
11 months ago

hi kushanNa ,

 

Thank you for suggesting a solution. However, the dax you provided is fixed to Version 2 - Version 1. It works if there are only 2 version. As mentioned, each Fiscal Qtr have multiple versions (more than 2 versions, up to users to generate as many version as applicable).

  • kushanNa's avatar
    kushanNa
    Icon for Super User rankSuper User
    11 months ago

    mbudiman 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

     

    • mbudiman's avatar
      mbudiman
      Icon for Helper III rankHelper III
      11 months ago

      hi kushanNa ,

       

      Thanks again for the revised DAX. It does not work correctly when I add records for version 3.