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
10 months ago

hi v-saisrao-msft 

 

I still have problem, the solution does not work in pbix. I am trying to figure out why. Do you have other suggestion to derive calculation of difference of Forecast Qty from previous version ?

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

try creating another new column

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

    Here is my actual pbix data structure with mockup data. When I apply solution suggested by you, it does not work. SalesForecast sample.pbix

  • 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.