IF and mobile calculations with Power query

carlos41 · Jan 22, 2020

Hello,

I am very new to power Query and I have been trying to sum and/or do some calculations in a row depending on the values that are in other row and doing mobile calculations depending on the value that is in another one.

So from the table in the bottom I would like to have something like this as an example:

L9M=
If KPI = "Assets" And Quarter= 2 then (SUM (from "Year.Quarter" = 20192 to "Year.Quarter" = 20184)) else
If KPI = "Assets" And Quarter= 4 then (SUM (("Assets") from "Year.Quarter" = 20194 to "Year.Quarter" = 20182)) else
If KPI = "EBIT margin..." And Quarter= 2 then (SUM ("EBIT")(from "Year.Quarter" = 20192 to "Year.Quarter" = 20184) /
(SUM("Revenues")(from "Year.Quarter" = 20192 to "Year.Quarter" = 20184)) end IF
the idea is that the mobile sums are always the same length, taking from the example above 3 values only. The data sheet has more years so I want the calculations to be done no matter the year.

thanks,

KPI

Quarter

Year.Quarter

Value

L9M

Assets

EBIT margin before special items

EBIT margin before special items
...
...
EBIT
...
...
..
Revenues
....
....
...
...

3

2

1

4

3

2

1

3

2

1

4

3

2

1
...
...
....
....
...
...
...
...
....
...

20193
20192
20191
20184
20183
20182
20181
20193
20192
20191
20184
20183
20182
20181
...
...
...
....
....
...
...
....
....

100
110
120
130
140
150
160
0,06
0,07
0,08
0,1
0,15
0,10
0,14
...
...
...
...
...
...
...
...
...

IF and mobile calculations with Power query

carlos41

New member