Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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.
Solved! Go to Solution.
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])
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
Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!
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])
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
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.
User | Count |
---|---|
19 | |
10 | |
10 | |
9 | |
7 |