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.

 

Fiscal QtrVersionVersion No (descending)Remark

FY26Q1

Oct-251Forecast for FY26Q1 as of Oct-25
FY26Q1Sep-25

2

Forecast for FY26Q1 as of Sep-25
FY26Q1Aug-253Forecast for FY26Q1 as of Aug-25

 

Here is the link for sample pbix data. I want to show Forecast Qty difference between versions as below. Pls advise. Thank you in advance. Forecast Difference from Prev.pbix

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

30 Replies

  • Ahmedx's avatar
    Ahmedx
    Icon for Super User rankSuper User
    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 Ahmedx ,

       

      Could you help me again. When I apply your solution, I dont get the same value of NumQuarter. I cant figure out why. Appreciate if you could advise if I miss something. Thank you. 

       

      What I did was create new Column for "NumQuarter" with the same formula as your pbix, but result is different. See below.

       

    • 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

         

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

    Hi mbudiman,

     

    You can achieve it by first clculating previous version forecast

     

    PrevVersionForecast =
    VAR CurrentFiscal = MAX('Forecast'[Fiscal Qtr])
    VAR CurrentVersionNo = MAX('Forecast'[Version No])
    RETURN
    CALCULATE(
    MAX('Forecast'[Forecast Qty]),
    FILTER(
    ALL('Forecast'),
    'Forecast'[Fiscal Qtr] = CurrentFiscal &&
    'Forecast'[Version No] = CurrentVersionNo + 1
    )
    )

    Then calculating the difference between them

     

    ForecastDifference =
    VAR CurrentForecast = MAX('Forecast'[Forecast Qty])
    VAR PreviousForecast = [PrevVersionForecast]
    RETURN
    IF(
    NOT ISBLANK(PreviousForecast),
    CurrentForecast - PreviousForecast,
    BLANK()
    )

     

    🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
    💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
    🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
    🔗 Curious to explore more? [Discover here].
    Let’s keep building smarter solutions together!

     

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

    I advise you to create a primary key to get the correct amount and to avoid it, so called "autexis"

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

    try pls

    Measure = 
VAR NumQuarter = SELECTEDVALUE('Sales Forecast'[NumQuarter])-1
VAR PQTY = 
CALCULATE([FCST_QTY], 'Sales Forecast'[NumQuarter]= NumQuarter , REMOVEFILTERS()
, SUMMARIZE('Sales Forecast','Sales Forecast'[SALES_REGION],'Sales Forecast'[SALES_COUNTRY],'Sales Forecast'[SALES_CHANNEL],'Sales Forecast'[PBU],'Sales Forecast'[SUB_MAIN_CAT_CODE],'Sales Forecast'[Part No])) 
RETURN
 [FCST_QTY] - PQTY