Compare Forecast Version

[ttcalendar]

Hello,

Every month we release a new revised forecast for our sales person to populate forward forecast numbers. I have another table which actual sales come through. When sales actualise, I need to calculate the variance between the previous month forecast and the actual sales but, then show for the following months, the revised forecast value to show the delta (difference row). 
I have slicer that shows the latest forecast version, eg, 202403 FC Version. 

Below are is an example snapshot what I'm looking for and the forecast table. 

Your help is much appreciated.

[ttcalendar]

Hi @v-rzhou-msft,

Thanks for the help! Much appreciated.

Instead of hard coding the forecast version in the calculate measures, I would like to present the user the option to select from 2 slicers to select the forecast versions they want to compare. 

How can I incorporate this?

[v-rzhou-msft]

Hi @ttcalendar ,

I am confused about what your table looks like. Is Actual data and Forecast data in different tables like my sample or in the same table?

Your calculation is based on your data model. Please share a sample file with me.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ttcalendar]

Hi @v-rzhou-msft 

Apologies, the actuals and forecasta data are in different tables like yours. Attached is a sample.  

Sample PBI 

[v-rzhou-msft]

Hi @ttcalendar ,

Due to I don't know your data model, I create a sample to have a test.

Forecast Table:

Fact Table:

Relationship:

Measures:

202402 FC Version = CALCULATE(SUM('Table'[Forecast]),FILTER('Table','Table'[FC Version] = "202402 FC Version"))

202403 FC Version = CALCULATE(SUM('Table'[Forecast]),FILTER('Table','Table'[FC Version] = "202403 FC Version"))

Acutual = CALCULATE(SUM(Fact[Sales]),FILTER(ALL('Fact'),Fact[Month] = MAX('Table'[Month])))

Difference = 
IF([Acutual] = BLANK(),[202403 FC Version] - [202402 FC Version] ,[Acutual] - [202402 FC Version])

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ttcalendar]

Thanks for the help! Much appreciated.

Instead of hard coding the forecast version in the calculate measures, I would like to present the user the option to select from 2 slicers to select the forecast versions they want to compare. 

How can I incorporate this?