Forum Discussion

mbudiman's avatar
mbudiman
Icon for Helper III rankHelper III
10 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
    10 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
10 months ago

does not work, QTY Diff does not show calculate correctly.

 

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

    hi Ahmedx ,

     

    It seems like you are calculating Qty difference between Fiscal Qtr instead of between 'Version No' of the same Fiscal Qtr. The solution does not work.

  • Ahmedx's avatar
    Ahmedx
    Icon for Super User rankSuper User
    10 months ago

    try changing 1 to 0

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

    hi Ahmedx , 

    It works partially for Version_No = 2 and Version_No =1, but not for Version_No = 0 (Preview).

  • Ahmedx's avatar
    Ahmedx
    Icon for Super User rankSuper User
    10 months ago

    will everything be at zero there?

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

    QTY DIFF should be compare for versions within the same Fiscal Qtr. When reach max Version (based on Version No) of the same Fiscal Qtr, there is no more version to compare, thus QTY DIFF should be blank. If there is only 1 version for that Fiscal Qtr, then there is no data to compare, thus QTY DIFF should be blank (or zero).

     

    See illustration below.

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

    hi Ahmedx , it does not really solve the problem, but it gives me clue. I managed to achieve result I want. Thank you for your time and assisting with solution. Appreciate it.