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 · Accepted solution]

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]

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.

[Ahmedx]

It seems you just need to change the format

[mbudiman]

hi @Ahmedx ,

Thats not what I meant. The value of NumQuarter for Fiscal Qtr "FY26Q1" and Version_No "1" should always be 20,260,101, but my pbix sum up all the rows of FY26Q1 and Version_No=1 to 78,244,510,062. Because of this, I dont get correct Forecast Qty difference.

[v-saisrao-msft]

Hi @mbudiman,

Have you had a chance to review the solution we shared by @Ahmedx  If the issue persists, feel free to reply so we can help further.

Thank you.

[mbudiman]

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]

try creating another new column

[mbudiman]

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

[Ahmedx]

pls try

[mbudiman]

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

[Ahmedx]

try

[mbudiman]

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]

try changing 1 to 0

[mbudiman]

hi @Ahmedx , 

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

[Ahmedx]

will everything be at zero there?

[mbudiman]

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.

[Ahmedx]

This is what I got, I don't know if this solves your problem

[mbudiman]

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.

[Ahmedx]

✅ How to fix it

You can tell Power BI not to summarize this column.

Steps:

1. In the Fields pane, find your column NumQuarter.

2. Right-click on it (or click the small arrow next to it).

3. Select “Default summarization” → “Do not summarize”.
   
   

[mbudiman]

hi @Ahmedx ,

Thank you for the solution. It works 😀