Conditional Slicers values for different years

[klehar]

Hi,

I have a peculiar problem. Heres a simple stripped down dataset

The green columns are my input.

Forecast(Red) is what I want as a slicer at an yearly level so that the user can select one forecast value for 1 year.

I want ideas around how should i have my Forecast slicer. 

Should the forecast value come from a single column (looks difficult since it would have a different  value for different years) ?

I cant afford to have 10 forecast value slicers for each year as in the actual dataset I have 10 years of data along with 5 such parameters including forecast which means I'll need to bring 50 slicers on the table.

Based on the forecast value, The New Value(Red) is calculated as Net Profit * (Forecast/100) + Net Value (that I can handle if the above is figured out)

Date	Gross Profit	Net Profit	Forecast	New Value
1/1/2020	11	5	5%	5.25
2/1/2020	42	10	5%	10.5
3/1/2020	77	1	5%	1.05
4/1/2020	77	10	5%	10.5
5/1/2020	97	10	5%	10.5
6/1/2020	69	7	5%	7.35
7/1/2020	16	3	5%	3.15
8/1/2020	50	2	5%	2.1
9/1/2020	67	2	5%	2.1
10/1/2020	15	6	5%	6.3
11/1/2020	23	6	5%	6.3
12/1/2020	90	5	5%	5.25
1/1/2021	96	2	6%	2.12
2/1/2021	24	4	6%	4.24
3/1/2021	80	8	6%	8.48
4/1/2021	53	7	6%	7.42
5/1/2021	95	4	6%	4.24
6/1/2021	46	7	6%	7.42
7/1/2021	94	4	6%	4.24
8/1/2021	64	8	6%	8.48
9/1/2021	75	10	6%	10.6
10/1/2021	85	4	6%	4.24
11/1/2021	95	7	6%	7.42
12/1/2021	61	7	6%	7.42

Finally, I want to add the New value on this matrix just below the Net Profit.

[v-yingjl · Accepted solution]

Hi @klehar ,

As far as I know, if you want the forecast value come from a single column, you have better to create multiple what-if parameters for each year such as 10 parameters for 10 years and use switch() statement to do the calculations.

About what-if parameters, please refer: Create and use what-if parameters to visualize variables in Power BI Desktop 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.