Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
I am trying to substarct the row1 to row2 and then the result will be substracted from row 3 and so on. I am looking for M Query NOT in DAX. Below is the sample data with expected result. Thanks for the help.
Delivery Week | Total Delivery | Expected Result |
31-Dec-23 | 25425 | 25425 |
6-Jan | 92 | 25333 |
13-Jan | 172 | 25161 |
20-Jan | 122 | 25039 |
27-Jan | 112 | 24927 |
3-Feb | 218 | 24709 |
10-Feb | 160 | 24549 |
17-Feb | 154 | 24395 |
24-Feb | 135 | 24260 |
2-Mar | 180 | 24080 |
9-Mar | 184 | 23896 |
16-Mar | 151 | 23745 |
23-Mar | 183 | 23562 |
30-Mar | 182 | 23380 |
6-Apr | 171 | 23209 |
13-Apr | 180 | 23029 |
20-Apr | 140 | 22889 |
27-Apr | 21 | 22868 |
Solved! Go to Solution.
there are so many running total solutions out there...
let
lst = List.Buffer(Table.ToRows(your_table)),
res = Table.FromList(
List.Generate(
() => [i = 0, c = lst{0}, s = c{1}],
(x) => x[i] < List.Count(lst),
(x) => [i = x[i] + 1, c = lst{i}, s = x[s] - c{1}],
(x) => x[c] & {x[s]}
),
(x) => x,
{"week", "delivery", "expected"}
)
in
res
there are so many running total solutions out there...
let
lst = List.Buffer(Table.ToRows(your_table)),
res = Table.FromList(
List.Generate(
() => [i = 0, c = lst{0}, s = c{1}],
(x) => x[i] < List.Count(lst),
(x) => [i = x[i] + 1, c = lst{i}, s = x[s] - c{1}],
(x) => x[c] & {x[s]}
),
(x) => x,
{"week", "delivery", "expected"}
)
in
res
User | Count |
---|---|
9 | |
8 | |
6 | |
6 | |
6 |