Unraveling Rolling Totals With Power Query

Imke Feldmann shows us how to get from rolling totals back to the original values using Power Query:

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:

(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)

That’s a pretty interesting approach.

Related Posts

The Power BI Visual Vocabulary

Jason Thomas has put together a great Power BI report: Note that there are some R/Python visuals and currently, R/Python visuals are not available on “Publish to Web”. Hence, I have just used a checkbox on the top of the report to show the images wherever R visuals are used (can be identified by the […]

Read More

Experimenting With Power BI Data Privacy Settings

Chris Webb takes us through some of the intricacies of Power BI data privacy settings and what it means when data sets are Private: Not only does it only show the Package Search endpoint, there is a warning that says:“Some data sources may not be listed because of hand-authored queries”This refers to the output step […]

Read More

Categories

August 2018
MTWTFSS
« Jul Sep »
 12345
6789101112
13141516171819
20212223242526
2728293031