Calculate difference between versions of Forecast data

[mbudiman]

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 Qtr	Version	Version No (descending)	Remark
FY26Q1	Oct-25	1	Forecast for FY26Q1 as of Oct-25
FY26Q1	Sep-25	2	Forecast for FY26Q1 as of Sep-25
FY26Q1	Aug-25	3	Forecast 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

[Ahmedx]

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

[Ahmedx]

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

[kushanNa]

Hi @mbudiman 

Please check if this is the output that you are looking for ? 

I have attached the pbix file with the dax for your reference 

[mbudiman]

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]

@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]

hi @kushanNa ,

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

[mbudiman]

hi @grazitti_sapna ,

I tried the DAX you provided. It does not work. Here is the pbix with DAX provided by you. Forecast Difference - solution from Sapna.pbix

[grazitti_sapna]

Hi @mbudiman,

Please find the solution in attached .pbix file

[grazitti_sapna]

Hi @Ahmedx,

Did it work for you?

[grazitti_sapna]

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!