Updating Year End Estimation based on Forecasting Cycle slicer

[veselovartem]

Dear Community,

Hoping for your help. I have a database containing both actual financials for the years and forecasted ones.

An example is shown below. ForecastCycle=0 means that these are in fact actual results.

ForecastCycle	ForecastYear	Year	Period	Value
0	2022	2022	1	100
0	2022	2022	2	200
0	2022	2022	3	300
0	2023	2023	1	400
0	2023	2023	2	500
0	2023	2023	3	600
0	2024	2024	1	700
0	2024	2024	2	800
0	2024	2024	3	900
1	2024	2024	1	1000
1	2024	2024	2	1100
1	2024	2024	3	1200
2	2024	2024	2	1300
2	2024	2024	3	1400
3	2024	2024	3	1500

I'm looking for a measure that will help me calculate Year End Estimation based on the slicer selection. For example, if a user chooses ForecastCycle=3 in a slicer, the following matrix table should be shown:

	1	2	3
2022	100	200	300	600
2023	400	500	600	1500
2024	700	800	1500	3000
	1200	1500	2400	5100

Meaning that the measure for Cycle=3 will take periods less than 3 and will take actuals for those, and for the remainder will take forecast. I tried to use CALCULATETABLE for this, but the measure returns me 

	1	2	3
2022	600	600	600	600
2023	1500	1500	1500	1500
2024	3000	3000	3000	3000
	5100	5100	5100	5100

instead of correct breakdown by periods.

Is it even possible to do so, given the structure of data, or it should be somehow changed to arrive at the desired result?

Thanks a lot.

[Kedar_Pande]

YearEndEstimation =
VAR SelectedCycle = SELECTEDVALUE('YourSlicerTable'[ForecastCycle])
RETURN
SUMX(
SUMMARIZE(
'YourDataTable',
'YourDataTable'[Year],
'YourDataTable'[Period],
"Value",
IF(
'YourDataTable'[ForecastCycle] < SelectedCycle,
SUMX(
FILTER(
'YourDataTable',
'YourDataTable'[ForecastCycle] = 0 &&
'YourDataTable'[Year] = EARLIER('YourDataTable'[Year]) &&
'YourDataTable'[Period] = EARLIER('YourDataTable'[Period])
),
'YourDataTable'[Value]
),
SUMX(
FILTER(
'YourDataTable',
'YourDataTable'[ForecastCycle] = SelectedCycle &&
'YourDataTable'[Year] = EARLIER('YourDataTable'[Year]) &&
'YourDataTable'[Period] = EARLIER('YourDataTable'[Period])
),
'YourDataTable'[Value]
)
)
),
[Value]
)

[Anonymous · Accepted solution]

Hi @veselovartem ,

You should create ForecastCycle  column as a slicer table, and then try formula like below:

YearEndEstimation = 
VAR SelectedCycle = SELECTEDVALUE('slicer'[ForecastCycle])
VAR ActualsUpToPeriod = 
    CALCULATE(
        SUM('Table'[Value]),
        'Table'[ForecastCycle] = 0,
        'Table'[Period] <= SelectedCycle
    )
VAR ForecastsFromPeriod = 
    CALCULATE(
        SUM('Table'[Value]),
        'Table'[ForecastCycle] = SelectedCycle,
        'Table'[Period] > SelectedCycle
    )
RETURN
    ActualsUpToPeriod + ForecastsFromPeriod

Best Regards,
Adamk Kong

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

[lbendlin]

Meaning that the measure for Cycle=3 will take periods less than 3 and will take actuals for those, and for the remainder will take forecast.

where are actuals and forecast in your sample data?

[veselovartem]

All actual results are in the database with ForecastCycle=0 indicator.

So, once a period closes, a new row with cycle=0 is added and actual results for the period.

[lbendlin]

How do you want to handle the actuals for 2024? Ignore them?