Forecast

[luv3yin]

Hi,

My data set is as below. What i'm trying to achieve is to calculate single month value, YTD value & FY value for actuals & forecast.

Scenario	Month	Value	Date
Actual	Jan	50	1/1/2019
Actual	Feb	150	2/1/2019
Actual	Mar	250	3/1/2019
Fcst 2 +10	Jan	50	1/1/2019
Fcst 2 +10	Feb	150	2/1/2019
Fcst 2 +10	Mar	200	3/1/2019
Fcst 2 +10	Apr	200	4/1/2019
Fcst 2 +10	May	200	5/1/2019
Fcst 2 +10	Jun	200	6/1/2019
Fcst 2 +10	Jul	200	7/1/2019
Fcst 2 +10	Aug	200	8/1/2019
Fcst 2 +10	Sep	200	9/1/2019
Fcst 2 +10	Oct	200	10/1/2019
Fcst 2 +10	Nov	200	11/1/2019
Fcst 2 +10	Dec	200	12/1/2019
Fcst 3 +9	Jan	50	1/1/2019
Fcst 3 +9	Feb	150	2/1/2019
Fcst 3 +9	Mar	250	3/1/2019
Fcst 3 +9	Apr	300	4/1/2019
Fcst 3 +9	May	300	5/1/2019
Fcst 3 +9	Jun	300	6/1/2019
Fcst 3 +9	Jul	300	7/1/2019
Fcst 3 +9	Aug	300	8/1/2019
Fcst 3 +9	Sep	300	9/1/2019
Fcst 3 +9	Oct	300	10/1/2019
Fcst 3 +9	Nov	300	11/1/2019
Fcst 3 +9	Dec	300	12/1/2019

Example:

There is one slicer for month. If selected month slicer is Mar, then forecast number should be reading from scenario Fcst 2+10.

	Single month	YTD	Full year
Actuals	250	450	450
Forecast	200	400	2200

How can i achieve this for forecast?

Thanks in advance!

[v-shex-msft]

HI @luv3yin ,

Your table contains multiple month values, I think month fields not suitable to find out correspond records and lookup related forecast records.
You can consider to use date field to instead or add a slicer with year field to help filter more accurate.

Regards,

Xiaoxin Sheng

[luv3yin]

Hi @v-shex-msft , 

I have slicer for year. Can you guide me through how to calculate for forecast in details? sorry, i'm quite new to this BI.

Thanks!

[v-shex-msft]

HI @luv3yin ,

Your data is hard to write a measure to dynamic calculation based on slicer, there are a few duplicate record exist in your table.(power bi data model not contains row index and column index)

For your requirement, I can only hard coding conditions to show correspondent result.

Regards,

Xiaoxin Sheng