Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
GKS1204
Regular Visitor

Forecast vs Prior Forecast variance

Hi 

 

I'm looking to do a varaince between multiple forecasts.  We have a new forecast each month and would like to dynamically figure out the varaince between forecasts.  Is the only way to create a new measure for each forecast scenario?   

 

I want to do something similar to using DATEADD, where i can easily pick up Prior year revenue but instead of prior year, i get the prior forecast revenue.  

 

I do have a calendar and Forecast Scenario table relationship in the data model.  

 

Data table.png

1 ACCEPTED SOLUTION
v-jingzhan-msft
Community Support
Community Support

Hi @GKS1204 

 

Here is a calculated column method:

Prior Forecast = 
VAR _posOfSpace = FIND(" ",'Table'[Forecast])
VAR _curNo = RIGHT('Table'[Forecast],LEN('Table'[Forecast])-_posOfSpace)
VAR _client = 'Table'[Client Code]
RETURN
MAXX(FILTER('Table','Table'[Client Code]=_client&&'Table'[Forecast]="Forecast "&(_curNo-1)),'Table'[Revenue])

vjingzhanmsft_0-1721810184371.png

 

Here is a measure method:

Variance = 
VAR _curRevenue = MAX('Table'[Revenue])
VAR _curForecast = MAX('Table'[Forecast])
VAR _curNo = MID(_curForecast,10,LEN(_curForecast)-9)
VAR _preRevenue = CALCULATE(MAX('Table'[Revenue]),ALLEXCEPT('Table','Table'[Client Code]),'Table'[Forecast]="Forecast "&(_curNo-1))
RETURN
_curRevenue - _preRevenue

vjingzhanmsft_1-1721811227584.png

 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

View solution in original post

2 REPLIES 2
v-jingzhan-msft
Community Support
Community Support

Hi @GKS1204 

 

Here is a calculated column method:

Prior Forecast = 
VAR _posOfSpace = FIND(" ",'Table'[Forecast])
VAR _curNo = RIGHT('Table'[Forecast],LEN('Table'[Forecast])-_posOfSpace)
VAR _client = 'Table'[Client Code]
RETURN
MAXX(FILTER('Table','Table'[Client Code]=_client&&'Table'[Forecast]="Forecast "&(_curNo-1)),'Table'[Revenue])

vjingzhanmsft_0-1721810184371.png

 

Here is a measure method:

Variance = 
VAR _curRevenue = MAX('Table'[Revenue])
VAR _curForecast = MAX('Table'[Forecast])
VAR _curNo = MID(_curForecast,10,LEN(_curForecast)-9)
VAR _preRevenue = CALCULATE(MAX('Table'[Revenue]),ALLEXCEPT('Table','Table'[Client Code]),'Table'[Forecast]="Forecast "&(_curNo-1))
RETURN
_curRevenue - _preRevenue

vjingzhanmsft_1-1721811227584.png

 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

Thank you!  I'm just learning VAR don't seem to work in excel, but i'm going to give this a shot in Power BI.  

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.

Top Kudoed Authors