- Power BI forums
- Updates
- News & Announcements
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Custom Visuals Development Discussion
- Health and Life Sciences
- Power BI Spanish forums
- Translated Spanish Desktop
- Power Platform Integration - Better Together!
- Power Platform Integrations
- Power Platform and Dynamics 365 Integrations
- Training and Consulting
- Instructor Led Training
- Dashboard in a Day for Women, by Women
- Galleries
- Community Connections & How-To Videos
- COVID-19 Data Stories Gallery
- Themes Gallery
- Data Stories Gallery
- R Script Showcase
- Webinars and Video Gallery
- Quick Measures Gallery
- 2021 MSBizAppsSummit Gallery
- 2020 MSBizAppsSummit Gallery
- 2019 MSBizAppsSummit Gallery
- Events
- Ideas
- Custom Visuals Ideas
- Issues
- Issues
- Events
- Upcoming Events
- Community Blog
- Power BI Community Blog
- Custom Visuals Community Blog
- Community Support
- Community Accounts & Registration
- Using the Community
- Community Feedback

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

- Power BI forums
- Forums
- Get Help with Power BI
- Power Query
- Re: Tricky Calculated Column - Calling any math gu...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Tricky Calculated Column - Calling any math gurus!

10-01-2023
11:30 PM

Hello,

I have a table of data that has two columns: ‘Week Number’ and ‘Rolling 4 Week Total’. I’ve made a simple example below. I don’t have data for weeks before - starting value for Week 1 is unknown.

Week Number | Rolling 4 Week Total |

88 | 35 |

89 | 35 |

90 | 42 |

91 | 43 |

92 | 42 |

93 | 45 |

94 | 44 |

95 | 53 |

96 | 59 |

97 | 49 |

98 | 43 |

99 | 37 |

100 | 34 |

I’m hoping there’s an equation for a calculated column that can calculate the ‘Actual Week Total’ – example results in table below. It may not be possible! I’d prefer in M but can work with DAX if easier. The real table is millions of rows long, with many products, markets and weeks.

Week Number | Rolling 4 Week Total | Actual Week Total |

88 | 35 | 10 |

89 | 35 | 12 |

90 | 42 | 15 |

91 | 43 | 6 |

92 | 42 | 9 |

93 | 45 | 15 |

94 | 44 | 14 |

95 | 53 | 15 |

96 | 59 | 15 |

97 | 49 | 5 |

98 | 43 | 8 |

99 | 37 | 9 |

100 | 34 | 12 |

Thanks in advance,

Dan

Solved! Go to Solution.

1 ACCEPTED SOLUTION

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

10-02-2023
08:44 PM

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

2 REPLIES 2

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

10-02-2023
08:44 PM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

10-03-2023
05:55 AM

Wow,

I really like this approach. I hadn’t thought of specifying a starting point, and given I’m working with years of data, it will work a treat.

Very well explained and detailed solution too. I am blown away!

In terms of granularity, I will create a ‘key’ to group products and markets.

Many thanks for all your time and effort in explaining this, I'm sure it will help others too!

Cheers!

Dan

Announcements

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Check out the February 2024 Power BI update to learn about new features.