Comparing columns directly next to each other in a pivot table, is this possible?
Dear all,
I have been trying to come up with a measurement where I can compare the current month with the previous month, the caveat is that it has to compare the direct previous month even if it has a null value.
I've been using
Alt(turnover) / After(tota FABS(turnover))-1 ,0)
and this works fine, as long as the columns have a value, however, if the direct next column contains Null() it will use the next first column that contains a value.
As can be seen in the table below: I would like to compare okt 2023 with sep 2023 and sept 2023 with aug 2023, etc. However, with the above mentioned measurement, it will compare the current value, with the next value. So it in the example table below, for example in the first row it will compare Okt - 2023 with Sep - 2023 correctly, but it will also compare Sep - 2023 to Jun - 2023 incorrectly. or in the last row, aug - 2023 with may - 2023.
Okt - 2023
Sep - 2023
Aug - 2023
Jul - 2023
Jun - 2023
May - 2023
Apr - 2023
100
150
-
-
50
100
-
-
-
50
-
-
100
-
-
-
100
-
-
50
200
I know this problem can be fixed easily by replacing the null() with dummy rows through the load script, however, i'm trying to find a solution where I don't need to add any dummy rows as dummy rows could impact the load times.
Can you please share some sample data set or data model to understand more? do you have master calendar implemented ? because ideally Before / After function doesn't skip null values if there is an row entry available.
Based on your sample I do see July with blank data, meaning you have an entry for July but with no data.
So we do have a master calendar implemented, that's why some months have null values but are still shown.
For the data sample, here is how it looks in db form:
ProductID, Amount, Date AproductA, 100, okt 2023 AproductA, 150, sep 2023 AproductA, 50, jun 2023 AproductA, 100, may 2023 BproductB, 50, aug 2023 BproductB, 100, may 2023 CproductC, 100, aug 2023 CproductC, 50, may 2023 CproductC, 200, apr 2023 DproductD, 100, Jul 2023
AproductA = the first row in the example table BproductB = the 2nd row in the example table CproductC = the 3th row in the example table DproductD = not in the example table but it just to fill jul 2023 so the logic behind is correct
