Forum Discussion

veselovartem's avatar
veselovartem
Regular Visitor
1 year ago
Solved

Updating Year End Estimation based on Forecasting Cycle slicer

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.

ForecastCycleForecastYearYearPeriodValue
0202220221100
0202220222200
0202220223300
0202320231400
0202320232500
0202320233600
0202420241700
0202420242800
0202420243900
12024202411000
12024202421100
12024202431200
22024202421300
22024202431400
32024202431500

 

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:

 123 
2022100200300600
20234005006001500
202470080015003000
 1200150024005100

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 

 123 
2022600600600600
20231500150015001500
20243000300030003000
 5100510051005100

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.

  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

     

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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's avatar
    lbendlin
    Icon for Super User rankSuper User
    1 year ago
    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's avatar
      veselovartem
      Regular Visitor
      1 year ago

      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's avatar
        lbendlin
        Icon for Super User rankSuper User
        1 year ago

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

         

         

         

  • Kedar_Pande's avatar
    Kedar_Pande
    Icon for Super User rankSuper User
    1 year ago
    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]
    )