Show actual values for previous months and forecast values for forecast month to future months

[AnushaMallepadd]

Hi,

I have sales data in single table for 5 scenarios(Actual, Budget, Jan Forecast, Mar Forecast and Jun Forecast).

Actual data is to be displayed for all the months in current year.

Budget data is to be displayed for all the months in current year.

But Forecast data is to be displayed for current year based on Forecast month.

If forecast month is January then Jan forecast sales data is available for whole year.

If forecast month is March then Mar forecast sales data is available only form March to future months of the year and previous months(Jan, Feb) will not have any forecast data.

If forecast month is June then Jun forecast sales data is available only form June to future months of the year and previous months(Jan, Feb,March,April,May) will not have any forecast data.

	Scenario
FiscalMonth	Actual	Budget	Jan Forecast	Mar Forecast	Jun Forecast
Jan-22	1000	800	1250
Feb-22	2000	900	1350
Mar-22	3000	1000	1450	1450
Apr-22	4000	1100	1550	1550
May-22	5000	1200	1650	1650
Jun-22	6000	1300	1750	1750	1750
Jul-22	7000	1400	1850	1850	1850
Aug-22	8000	1500	1950	1950	1950
Sep-22	9000	1600	2050	2050	2050
Oct-22	10000	1700	2150	2150	2150
Nov-22	11000	1800	2250	2250	2250
Dec-22	12000	1900	2350	2350	2350

Here i want to show the Actual data in place of Empty forecast months.

I have tried doing this by creating a simple DAX measure, but it is not working as expected.

If (ISBLANK([March Forecast]) && SELECTEDVALUE('Date' [FiscalYearId]) = 22, [Actual], [Forecast])

Can some one please help me to solve this.

Thanks,

Anusha

[amitchandak · Accepted solution]

@AnushaMallepadd , Try like

If (ISBLANK([March Forecast]) && Max('Date' [FiscalYearId]) = 22, [Actual], [Forecast])

or

If (ISBLANK([March Forecast]), [Actual], [Forecast])

[AnushaMallepadd]

Hi,

Thanks for your response. This is working when the amount is sliced at higher grain like Fiscal month, but not working when sliced with Scenario.