Month-over-Month Percentage Change

Phil · June 11, 2026, 2:29pm

Hi, could we please add a transformation called Month-over-Month Percentage Change / MoM % Change?

Problem:
The dataset contains one row per month and category. Each row has the current value, but the previous month’s value is not directly available in the same row.

Example:

Month	Category	Value
1	A	100
2	A	120
3	A	90
1	B	50
2	B	75

To calculate the percentage change, the transformation first needs to find the value from the same category in the previous month.

Expected result:

Month	Category	Value	MoM % Change
1	A	100	NULL
2	A	120	20%
3	A	90	-25%
1	B	50	NULL
2	B	75	50%

Formula:

(current_month_value - previous_month_value) / previous_month_value * 100

For the first month, or if no matching previous-month value exists, the result should be NULL.

In abstract terms:
The transformation should calculate the relative percentage change between a current row and the matching previous-period row, grouped by the selected dimension/category.

Olaf · June 11, 2026, 3:52pm

you can do it without new transformation, it isn’t straightforward (maybe there are faster ways). The main trick is the Offset transformation to move values up or down in different rows.

Phil MoM change.transform (6.9 KB)

Admin · June 11, 2026, 8:46pm

I think that is a bit niche for it’s own transform.

That is the way we would have done it.

Phil · June 12, 2026, 6:43am

thank you olaf but the problem is more complex:

Problem:
The dataset contains one row per month and category. Each row has the current value, but the previous month’s value is not directly available in the same row.

In addition, the month alone is not enough for matching. The data can contain multiple categories per month, for example:

Month Category Value

1 A 100

1 B 50

1 C 200

2 A 120

2 B 75

2 C 180

So the transformation must not simply compare month 2 with the previous row.
It has to compare:
2 / A with 1 / A
2 / B with 1 / B
2 / C with 1 / C
and not accidentally mix values like:
2 / A with 1 / B
2 / B with 1 / C
Expected result:

Month Category Value MoM % Change

1 A 100 NULL

1 B 50 NULL

1 C 200 NULL

2 A 120 20%

2 B 75 50%

2 C 180 -10%

Formula:
(current_month_value - previous_month_value) / previous_month_value * 100
In abstract terms:
The transformation should find the matching previous-period row based on period + selected grouping dimension(s), then calculate the percentage change. This is important to avoid mixing categories within the same month.

Phil · June 12, 2026, 7:38am

maybe not as new transform maybe as function for example in calculate transform- i would not need the percentage - that is not my pain, my pain - is the right grouping for each period

Olaf · June 12, 2026, 7:54am

Two sort statements should solve it the first one a sort by Category and Month as second criteria (both Ascending). This should be done before the other logic. Second sort after calculation logic: Sort be Month and here Category as second criteria.

Olaf · June 12, 2026, 8:21am

attached 2 solution, the upper part includes the sorts as stated in the reply before.

The bottom part created a key (with concat) and looks up the percentage later on, so the row order (whatever it might be is the same in the result.

Phil MoM change 2.transform (16.5 KB)

Phil · June 12, 2026, 8:24am

thanks Olaf i look into:)

Phil · June 12, 2026, 9:57am

the percentages are off with my example - it is not really stable

Olaf · June 12, 2026, 10:00am

Can give me your real data to check (and explanation where it is unstable). @Admin: Can you give @Phil my mail address, so it doesn‘t to be published.

Phil · June 12, 2026, 10:07am

sample dataset.csv (1.7 KB) sure the essential columns are - Period, Cat_L4 and Value Thank you:)

Admin · June 12, 2026, 10:45am

And what rows are you getting an unexpected value at? What is the expected value?

Olaf · June 12, 2026, 11:04am

I think it works correct, there is an issue with th exhuming values from negative to positive and vice versa. It will create percentages with changing signs and the jumps are large and show therefore percentages up in thousands.
And if you go from -10 to 20, you get a a percentage of -200%

the table shows a series of Values sorted by Cat_L4 and period.

Complete calculation in the transform file
Phil MoM change 3.transform (18.5 KB)

Phil · June 12, 2026, 11:33am

hi olaf, thanks again- i think i understand why the percentages were off look at the screenshot: the first calculations is correct: calculate tmp

Olaf · June 12, 2026, 11:39am

nevertheless the calculation in my file were not according to your calculation:

I changed it (and there was an issue with column names) and it gives now (just the same, but now the formulas are according to the given one)

Phil MoM change 4.transform (18.5 KB)

Phil · June 12, 2026, 8:36pm

thanks so much u are right- the right formula should be (current month/ previous month)*100 again dont trust always the AI….

Phil · June 12, 2026, 9:31pm

Offset is the wrong approach here because it only shifts values by row position.

That means EDT takes the value from the row above or below after sorting. It does not really check whether this row belongs to the correct previous month and the same category combination.

Example problem:

Period 1 / A / B / C / D / Value 100
Period 1 / A / B / C / E / Value 200
Period 2 / A / B / C / D / Value 120
Period 2 / A / B / C / E / Value 250

With Offset, the result depends completely on the current sort order. If the order changes, or if one category is missing in one month, the previous value can be taken from the wrong row.

For example, it could accidentally compare:

Period 2 / A / B / C / D
with
Period 1 / A / B / C / E

But the correct comparison must always be:

Period 2 / A / B / C / D
with
Period 1 / A / B / C / D

So the match must not be based on row position.
It must be based on a unique key.

Correct approach

Create two keys:

1. Current_Key

Current_Key =
Period + "/" + Cat_L1 + "/" + Cat_L2 + "/" + Cat_L3 + "/" + Cat_L4

Example:

2/A/B/C/D

2. Previous_Key

Previous_Key =
Previous_Period + "/" + Cat_L1 + "/" + Cat_L2 + "/" + Cat_L3 + "/" + Cat_L4

where:

Previous_Period = Period - 1

Example for period 2:

1/A/B/C/D

Lookup logic

The lookup must be configured like this:

Top key column:      Previous_Key
Bottom key column:   Current_Key
Bottom value column: Value
Matching:            Exact
No match:            empty / NULL

Meaning:

Take the Previous_Key of the current row,
find the matching Current_Key in the dataset,
and return the Value from that matched row.

Example:

Current row:

Period 2 / A / B / C / D / Value 120
Previous_Key = 1/A/B/C/D

Lookup finds:

Current_Key = 1/A/B/C/D
Value = 100

Result:

Previous_Value = 100

Offset = row-based → unsafe
Lookup with Previous_Key → key-based → correct

Admin · June 12, 2026, 10:49pm

Another possible approach might be to use Cross to create all the possible keys and then use Join to join with the actual keys. Whether this is practical depends on how many possible cobinations there are.

Olaf · June 13, 2026, 8:55am

I understood before, the key ist Cat_L4. But I get your issue if the period sequence is broken. I think offset is still the right approach and only some adaptations are necessary.

I create now two keys with Concat Col and sort similar as before.One key is “Cat-Key” out of the 4 Lx values and the “Key” ist period plus all 4 Lx values.

Now the columns Period tmp (a copy of Period and the value and Cat-Key tmp column are moved with offset. Now a calculation is added (subtraction of Period - Period tmp). If this result is 1 it is a subsequent month. Therefore the if statement can be extended with an AND condition, so the value tmp2 is only populated if the Cat-Keys are identical AND it is a consequent month sequence.

The other percentage calculations are not touched (if required, I think you can do it by yourself).

Phil MoM change Key suggestion.transform (11.9 KB)

Phil · June 15, 2026, 9:58pm

Thanks Olaf - for ur feedback i really appreciate- this way works very good as well