Recalculating Average after a Point

[Nankaina]

Hi all,

I want to show a line graph with an average line that shifts about halfway through at a known point. Looking through the forums, I've seen ways to make moving averages, averages by year, or averages by a value in another column, but not based on before/after a single point.

For a concrete example, I made a table of dummy data using RandBetween with ranges of (100,150), (130,170), and (150,200) to represent three distinct averages. The table is shown below, with shifts happening in Mar-23 and Nov-23:

Month	Value
Jul-22	138
Aug-22	138
Sep-22	148
Oct-22	130
Nov-22	141
Dec-22	143
Jan-23	150
Feb-23	101
Mar-23	135
Apr-23	163
May-23	158
Jun-23	151
Jul-23	145
Aug-23	150
Sep-23	142
Oct-23	140
Nov-23	185
Dec-23	161
Jan-24	166
Feb-24	177
Mar-24	172

Based on the locations of the shifts, the created graph should look something like this:

A normal average line would go above all the points on the left and below all the points on the right. This clearly shows three different averages and the months where they changed. The visual will be filterable, so any solution would need to work if some of the x-axis is hidden.

[lbendlin]

Based on the locations of the shifts

Where are these shifts defined?

[Nankaina]

Apologies! These are manually chosen based on known changes in the data. They aren't recorded in the data currently (though I could easily add a column that denotes which months should start a new average, if that would help).

[lbendlin]

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[Nankaina]

I did.

The table listed shows all values needed. The line graph shows that same data with three averages: from Jul-22 to Feb-23, from Mar-23 to Oct-23, and from Nov-23 to Mar-24. Where one average stops and the next starts isn't calculated, it's known from external sources. In this case, I know there is a change after Feb-23 and a change after Nov-23.

[lbendlin]

You could argue if that change was after February or after February plus March.

you could suggest the shifts after an absolute change over 40,  or a positive change over 30 . or in many other ways.  Are these shifts defined by you, the report designer, or are they defined by your report users dynamically?

[Nankaina]

The shifts are defined by the report designer, and do not dynamically change.

[lbendlin · Accepted solution]