Run Rate without Dates but by periods and with a slicer for Funder.

[UrAvgWally]

Hi All,

Could anyone please help me with the following?

Basically, I want to calculate a runrate for the following data for example , let is call it RunRateTable:

Assuming I am in current period 5, and I have a table Setup[CP] which indicates number 5 on the first row. How would I calculate a runrate?

There are 13 periods in total and values assigned for each in the Value column of the table, however Period 1 to 5 are actuals 

And Period 6 to 13 are considered future periods. So I would want to replace the values as runrates based on 1-5 performance .

Since there the summarized table, is also summarized by funder, the "Periods" are duplicating. If I removed the column funder and summarised it would have been easy, but I really need the column funder to stay, as it links with a table let's say RSKCL[Funder ] , as a relationship which is a slicer for my dashboard.

As always, any help would be greatly appreciated!

[tamerj1 · Accepted solution]

Hi @UrAvgWally 
Please try 

RunRate =
VAR RefPeriod =
    MAX ( Setup[CP] )
VAR CurrentPeriod = Table1[PeriodNo]
VAR CurrentValue = Table1[Value]
VAR CurrentFunderTable =
    CALCULATETABLE ( Table1, ALLEXCEPT ( Table1, Table1[Funder] ) )
VAR FilteredTable =
    FILTER ( CurrentFunderTable, Table1[PeriodNo] <= RefPeriod )
VAR RunRate =
    AVERAGEX ( FilteredTable, Table1[Value] )
RETURN
    IF ( CurrentPeriod <= RefPeriod, CurrentValue, RunRate )

[UrAvgWally]

Perfect. You're a star. Thank you so much 🙂