•3 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Power Query - Create Multiple Running Totals for Different Time Intervals
Our take
Are you struggling to generate meaningful insights from your project data? Power Query offers an innovative solution by allowing you to create multiple running totals for different time intervals. This guide will walk you through the steps to add new columns for overall running totals, monthly running totals, in-month calculations, and static monthly totals for your datasets. By mastering these techniques, you'll transform your data analysis process, enabling more informed decision-making. Let's dive in and elevate your spreadsheet capabilities!
Hi,
This is the sample layout of my dataset:
| Week | Month | Res | Project | Area | Val1 | Val2 | Val3 |
|---|---|---|---|---|---|---|---|
| 4-Apr-25 | Apr-25 | IT | Proj 01 | North | 50 | 100 | 150 |
| 11-Apr-25 | Apr-25 | IT | Proj 01 | North | 100 | 150 | 200 |
| 18-Apr-25 | Apr-25 | IT | Proj 01 | North | 150 | 200 | 250 |
| 2-May-25 | May-25 | IT | Proj 01 | North | 200 | 250 | 300 |
| 4-Apr-25 | Apr-25 | IT | Proj 02 | South | 10 | 20 | 30 |
| 11-Apr-25 | Apr-25 | IT | Proj 02 | South | 20 | 30 | 40 |
| 18-Apr-25 | Apr-25 | IT | Proj 02 | South | 30 | 40 | 50 |
| 2-May-25 | May-25 | IT | Proj 02 | South | 40 | 50 | 60 |
Im trying to generate this table in Power Query which will add 4 new grouped running total columns for each value columns:
Running Total till date
Running Total Monthly
Running Total Inmonth Only
Monthly Total Static Value
This is the sample output for Val1 column. Will need it repeated for Val2 and Val3 columns also:
| Week | Month | Res | Project | Area | Val1 - RT Overall | Val1 - RT Monthly | Val1 - RT InMonth | Val1 - RT ThisMonth |
|---|---|---|---|---|---|---|---|---|
| 4-Apr-25 | Apr-25 | IT | Proj 01 | North | 50 | 300 | 50 | 300 |
| 11-Apr-25 | Apr-25 | IT | Proj 01 | North | 150 | 300 | 150 | 300 |
| 18-Apr-25 | Apr-25 | IT | Proj 01 | North | 300 | 300 | 300 | 300 |
| 2-May-25 | May-25 | IT | Proj 01 | North | 500 | 500 | 200 | 200 |
| 4-Apr-25 | Apr-25 | IT | Proj 02 | South | 10 | 60 | 10 | 60 |
| 11-Apr-25 | Apr-25 | IT | Proj 02 | South | 30 | 60 | 30 | 60 |
| 18-Apr-25 | Apr-25 | IT | Proj 02 | South | 60 | 60 | 60 | 60 |
| 2-May-25 | May-25 | IT | Proj 02 | South | 100 | 100 | 40 | 40 |
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Power Query Start Year to Year convertHello Everyone. A monthly report is generated as shown on the left. I am hoping to use Power Query to adjust the columns as shown on the right so the total cost per year is easier to visualize. Is there a simple way to use PQ for this? It isn't too hard to manually filter and do this but I'd like to remove any manual steps if possible. Data Desired Output Project Name Project Start Year Year 1 Year 2 Year 3 Year 4 Project Name Project Start Year 2024 2025 2026 2027 2028 2029 Alpha 2024 1,000.00 600.00 200.00 0.00 Alpha 2024 1,000.00 600.00 200.00 0.00 0.00 0.00 Bravo 2025 200.00 0.00 0.00 0.00 Bravo 2025 0.00 200.00 0.00 0.00 0.00 0.00 Charlie 2024 500.00 500.00 500.00 100.00 Charlie 2024 500.00 500.00 500.00 100.00 0.00 0.00 Delta 2026 200.00 100.00 0.00 0.00 Delta 2026 0.00 0.00 200.00 100.00 0.00 0.00 Echo 2026 1,500.00 1,000.00 500.00 250.00 Echo 2026 0.00 0.00 1,500.00 1,000.00 500.00 250.00 Foxtrot 2025 1,000.00 1,000.00 1,000.00 1,000.00 Foxtrot 2025 0.00 1,000.00 1,000.00 1,000.00 1,000.00 0.00 submitted by /u/XEP19 [link] [comments]
- Modified PIVOTBY or dynamic array formula to lookup across rows and columnsHi I have the following source data: Week Project Area Target Actual 4-Apr-25 Proj 01 North 50 100 11-Apr-25 Proj 01 North 150 120 18-Apr-25 Proj 01 North 300 50 2-May-25 Proj 01 North 500 70 4-Apr-25 Proj 02 South 10 200 I need a single dynamic formula to spill across rows and columns to give this result : Project Area Measure 4-Apr-25 11-Apr-25 18-Apr-25 2-May-25 Proj 01 North Target 50 100 300 500 Proj 01 North Actual 100 120 50 70 Proj 02 South Target 10 - - - Proj 02 South Actual 200 - - - I tried a PIVOTBY solution but couldn't quite achieve this result, or another option is to go for fixed values for the first three columns & dates and a spilling formula for the values submitted by /u/land_cruizer [link] [comments]