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

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!