I get data for utility bills and the date range for the utility meter readings are not always consistent, I'm hoping Power Query can provide a more simple solution. I'm currently doing this with like 3 dozen helper columns to try to work it out in excel and there are still edge cases where it doesn't know what to do and breaks.
My data is going to be in a table that has "Building Name", "Meter Name", "Start Date", "End Date", and "Usage". Some times the start date and end date are exactly one month from the 1st of the month to the end of the month, other times the date range starts and ends in the middle of the month, or sometimes they span multiple months. For example:
- 1/1/2025 - 1/31/2025
- 1/13/2025 - 2/17/2025
- 12/20/2024 - 3/20/2025
I would like to be able to find the number of days that occur in each month (keeping track of the year) and find the proportional usage that occurred in that month. It would calculate the number of days in the start date to end date range, find usage per day, calculate the usage per day times the number of days that occurred in each month, and merge broken up months together.
So if two rows were 1/15/2025 - 2/2/2025 with a usage of 100,000 and 2/3/2025 - 3/1/2025 with a usage of 90,000. It would say there are 18 days in the date range with 5555.5 usage per day. 16 of those days are in Jan, 2025 and 2 of those days are in Feb, 2025. For the second date range there are 26 days and 25 of them are in Feb, 2025, and 1 of them is in March with a usage of 3,461.5 per day.
First Range
5555.5 * 16 = 88,888.89 usage for Jan, 2025
5555.5 * 2 = 11,111.11 usage for Feb, 2025
Second Range
3461.5 * 25 = 86,538.46 usage for Feb, 2025
3461 * 1 = 3,461.5 usage for Mar, 2025
Final Output
Jan, 2025 usage = 88,888.89
Feb, 2025 usage = 11,111.11 + 86,538.46 = 97,649.57
Mar, 2025 usage = 3,461.5
This would need to be calculated separately for each Meter for each Building. If it's easy to think of in accounting terms it would be similar to finding sales each month per salesperson per location. Attached is some sample data.
@campbell83
I just added two columns to your table so that I didn't have to do it in PQ. See if you can follow the steps in PQ which aren't more complicated than adding a custom column that lists numbers from 0 to the number of days in the period. Expand this list and add a date column based on start date plus the number from the custom column. Clean up a bit and load back to Excel. Now you are all set to create a simple pivot table.
See attached.