Forum Discussion

maxabele's avatar
maxabele
Frequent Visitor
5 years ago

Turnover Forecast

Hi guys,

 

I searched the whole morning but couldn't find a solution to my problem. 

My goal is to forecast FTE development based on current FTEs + forecasted turnover rates for the future. 

I have two tables:

Table 1:

FTE numbers in long data format, i.e. Dates and FTE values in rows, not columns

 

Table 2: 

Turnover rates in long data format, i.e. Dates and Turnover rates in rows

 

The two tables are connected with an identifier: Code (Company & Cost Center) + Date.

 

 

I want to prevent doing manual work. I need a measure that takes the FTE number from the last month, applies this month's turnover rate to calculate this month's FTE number. Expressed differently FTE_thismonth = FTE_lastmonth * (1-TurnoverRate_thismonth). The tricky part is, it should then use this outcome as the basis for the further calculations.

 

Hope anybody can help me with that. Thanks in advance,

Max

 

 

 

 

 

Need Help

1 Reply

  • lbendlin's avatar
    lbendlin
    Super User
    5 years ago

    Power Query has a List.Accumulate function that can do all that (and do all your dishes too)

     

    https://docs.microsoft.com/en-us/powerquery-m/list-accumulate

     

    If, however, you need to do that in DAX then you will have to resort to things like PRODUCTX() but they are less elegant and quite a bit slower.

     

    If you want a sample solution then please provide sample data