Recently I came across an interesting request where someone wanted to un-cumulate their quarterly YTD-figures (green) into their single quarters values (red) like so (“Unravel cumulative totals”):
Task
Retrieve every Quarters Amount from the Quarter To Date values (“YAmount”)
Method
To retrieve this value, one would have to start with the first value in the year. This is also the value of the first quarter, but for the 2nd quarter, one would have to deduct the value of the first quarter from the cumulative value of the 2nd quarter. So basically retrieving the previous cumulative row and deduct it from the current cumulative row. Do this for every row, unless it’s the start of the year or belongs to a different account code in this example:
Grab previous cumulative values, but only within the valid ranges(Although for the data given in the sample, it would be sufficient to just take the year as a discriminator, but to be on the save side, I would suggest to include the different accounts as well)
Solution
Fortunately I’ve already written a function to grab the previous rows with lots of bells and whistles, that also includes the option to include grouping parameters. So if you copy the function code to the advanced editor of an empty query and name this “fnGetPreviousRow”, you just have to add a new step with the following code:
fnGetPreviousRow(#"Changed Type", null, {"YAmount"}, {"Account code", "Year"}, null, null)
Add a step to call this function (don’t go via “Add a column” here !!)
Call function (Previous stepname: red, Amount column: yellow, Grouping columns: green)
This will retrieve the previous row from the cumulative “YAmount” within every combination of “Account code” & “Year” and fill in nulls in the respective first rows. So when you then add another column that subtracts the new Value from the CumTotal, you will retrieve nulls for the first rows. This is not the desired outcome and I suggest to go back to the previous step -> check the “YAmount.Prev”-column and replace “null” by “0”. After that the calculation returns the correct result:
Result with single quarterly values (“Unravel cumulative totals”)
File to download
You can download the file to follow the steps: Unravel cumulative or running totals
Enjoy & stay qeryious 😉
Pingback: Unraveling Rolling Totals With Power Query – Curated SQL
Another way to go from cumulative to periodic view is merging it with a lookup table that reverses Q1 into Q2, Q2 into Q3 and Q3 into Q4:
Old Q, New Q, Multiplier
Q1, Q1, 1
Q1, Q2, -1
Q2, Q2, 1
Q2, Q3, -1
Q3, Q3, 1
Q3, Q4, -1
Q4, Q4, 1
Then new column [QAmount] is [YAmount] * [Multiplier]
Then clean it up by grouping by New Q.