Skip to Content
0
Jan 15, 2021 at 09:29 AM

Dynamic moving cell calculation using cell references in query designer

36 Views Last edit Jan 15, 2021 at 12:28 PM 3 rev

Dear guys, I am faced with the following issue: I created a query with two structures to use the cell editor. One structure is a pure fiscper structure using offsets. The other keyfigure structure uses formulas/selection. My problem now: Due to rolling fiscal periods the formulas in the cell editor have to be adjusted dynamically depending on the fiscal period in the column.

For example:

Column M is calculated using M12+L11+K11

screenshot-10.png

Now column N is calculated using L11+M12+N12 (so just move one right and one row down)

screenshot-11.png

Due to moving fiscal periods the formula has to adjusted each rolling months as we only visualize the last 13 months.

How could I do such a dynamic change of calculation without manual adjustment of the formula each month?

Tried to use "help cells" but not knowing how these should be implemented to use them.

Would be glad about any hints here.

Thanks so far.

Regards,

Peter

Attachments

screenshot-10.png (42.1 kB)
screenshot-11.png (230.2 kB)