Tricky Calculated Column - Calling any math gurus!

Hi DanYuncken 

It is possible to derive Actual Week Total values, but there is not a unique solution.

If you begin with rolling 4 week totals, you can specify an initial set of 3 weeks' actual values, then the remaining weeks can be determined.

I have attached a small example using DAX since it was easier to knock together, but I think this is really a job for Power Query.

Here is the code for the calculated column I created. You can see the initial condition in the InitialCondition variable. The exact weeks covered by the initial condition could be adjusted.

Actual Week Total = 
-- Number of weeks summed in each Rolling Total
VAR RollingWeeks = 4
-- This table should have a row count = ( RollingWeeks - 1 )
VAR InitialCondition =
    DATATABLE (
        "WeekNum", INTEGER,
        "Actual", DOUBLE,
        {
            { 88, 10 },
            { 89, 10 },
            { 90, 10 }
        }
    )
VAR InitialConditionMaxWeek =
    MAXX ( InitialCondition, [WeekNum] )
VAR InitialConditionMinWeek =
    MINX ( InitialCondition, [WeekNum] )
VAR InitialConditionSum =
    SUMX (
        InitialCondition,
        [Actual]
    )
VAR ActualAfterInitial =
    LOOKUPVALUE (
        Data[Rolling 4 Week Total],
        Data[Week Number], InitialConditionMaxWeek + 1
    )
    - InitialConditionSum
VAR InitialConditionExtended =
    UNION (
        InitialCondition,
        ROW (
            "WeekNum", InitialConditionMaxWeek + 1,
            "Actual", ActualAfterInitial
        )
    )
VAR CurrentWeek = Data[Week Number]
VAR ActualWeekTotal =
    IF (
        CurrentWeek <= InitialConditionMaxWeek + 1,
        MAXX (
            FILTER ( InitialConditionExtended, [WeekNum] = CurrentWeek ),
            [Actual]
        ),
        VAR BaseWeekRow =
            FILTER (
                InitialConditionExtended,
                MOD ( [WeekNum], RollingWeeks ) = MOD ( CurrentWeek, RollingWeeks )
            )
        VAR BaseWeek =
            SELECTCOLUMNS ( BaseWeekRow, "WeekNum", [WeekNum] )
        VAR BaseWeekActual =
            SELECTCOLUMNS ( BaseWeekRow, "Actual", [Actual] )
        VAR DifferenceWeeks =
                TREATAS (
                    GENERATESERIES ( BaseWeek + RollingWeeks, CurrentWeek, RollingWeeks ),
                    Data[Week Number]
                )
        VAR Differences =
            CALCULATE (
                SUMX (
                    Data,
                    VAR CurrentWeekInner = Data[Week Number]
                    VAR PreviousWeekInner = CurrentWeekInner - 4
                    RETURN
                        Data[Rolling 4 Week Total]
                        - LOOKUPVALUE ( Data[Rolling 4 Week Total], Data[Week Number], PreviousWeekInner )
                ),
                DifferenceWeeks,
                REMOVEFILTERS ()
            )
        RETURN
            BaseWeekActual + Differences
    )

RETURN ActualWeekTotal

The basic formula I applied (after doing some algebra) is:

Let x(n) be the actual value for week n.

Let R(n) be the rolling total for week n.

Let D(n) = R(n) - R(n-1) (i.e. the difference between two consecutive rolling totals).

Then assume we have initial condition specified for weeks k, k+1 and k+2 (in the case of 4 week rolling totals), that is we know x(k), x(k+1), x(k+2).

Then x(k+3) = R(k+3) - [ x(k) + x(k+1) + x(k+2) ].

Then the pattern after this is

x(k+4) = x(k) + D(k+4)

x(k+5) = x(k+1) + D(k+5)

x(k+6) = x(k+2) + D(k+6)

x(k+7) = x(k+3) +  D(k+7)

x(k+8) = x(k) + D(k+4) + D(k+8)

x(k+9) = x(k+1) + D(k+5) + D(k+9)

....

In other words, each x(n) is the sum of a "base value" x(i) taken from x(k) to x(k+3), such that n is equivalent to i modulo 4, plus a sequence of differences R(n), R(n-4),.... at regular intervals.

(this can be expressed more precisely but hopefully makes sense).

Given that you have multiple dimensions to worry about (products, markets etc), you would need to do these calculations at the right level of granularity.

Does the above logic seem right?

I can come back to you with an M example of this as well later on 🙂

Regards