Forum Discussion
Dynamic date relationship & Measures for data modelling for a use case
Power BI Data Model Question – Allocating Utility Charges by Service Date
I need advice on the best data model for allocating utility charges across calendar months.
My utility table has one row per meter/line item per bill, with fields such as:
- Building
- Bill ID
- Utility type (electric, gas, water, etc.)
- Read From
- Read To
- Days of Service
- Amount
- Total Current Charges
For example, a bill for Building A has $100 for 30 days, with:
- Read From: June 15
- Read To: July 14
I want to allocate the $100 across the actual service dates:
- June: 16 days → $53.33
- July: 14 days → $46.67
So when I select June in my Calendar table, I want to see $53.33 for Building A, and when I select July, I want to see $46.67.
Currently, I have an active relationship:
Calendar[Date] → Utility[Read To]
This causes incorrect monthly results because a bill spanning two months is associated only with its Read To date.
What is the recommended Power BI data model for this?
- should this be represented per building as well?
My vision,
When I select a month, year, and/or building, I need to report:
- Selected Month Total
- YTD Total
- % Change vs. Prior Year
- Average Daily Rate for the selected month
- Daily/allocated rate by Building for the selected month
I'd especially appreciate advice on the best-practice star-schema approach and performance considerations for a large dataset.
I would distribute the bill's amount across its service dates.
You can split a single row into multiple rows using Power Query but I'd do this with a small dataset as this can be very slow with a large one.
Alternatively, you can use DAX measures and a disconnected dates table to spread the amount calculate its value for a specific period.
Please see the attached pbix.
10 Replies
For this kind of period allocation, I would move the calculation into Power Query rather than trying to solve it in DAX at the bill grain. Keep your bill table as it is, then duplicate it and expand each row into one row per day of service, with a per-day amount equal to Amount / Days of Service. Build the relationship as Calendar[Date] to that new daily fact table, and the monthly split falls out naturally.
In Power Query the expand step looks like this:
= Table.AddColumn(Source, "Date", each List.Dates([Read From], [Days of Service], #duration(1,0,0,0)))
Then expand the Date column to new rows and add a DailyAmount column equal to [Amount] / [Days of Service]. A single-direction relationship from Calendar[Date] to Utility_Daily[Date] then handles slicers on any date grain, and totals stay additive.
If you must stay at the bill grain, the DAX equivalent is an events-in-progress pattern using SUMX over Calendar with a filter on Read From <= Date <= Read To, but the daily fact table is almost always the cleaner long-term model.
If this helped, a thumbs up and marking it as the solution would be appreciated.
Thanks,
Shai KarmaniI would distribute the bill's amount across its service dates.
You can split a single row into multiple rows using Power Query but I'd do this with a small dataset as this can be very slow with a large one.
Alternatively, you can use DAX measures and a disconnected dates table to spread the amount calculate its value for a specific period.
Please see the attached pbix.
Hi arthiannadi06 ,
Please try attached PBIX.
i have tried to find the start month from ReadFrom and Readto and splitted data based on that.
Please give kudos or mark it as solution once confirmed.
Thanks and Regards,
Praful
This needs a bridge/allocation fact table, not a direct relationship, since one bill row has to spread across multiple months.
Best approach, in Power Query, expand each bill into one row per day (or per month if daily grain is too heavy) within its Read From/Read To range, with a pro-rated amount per row. That table relates cleanly to Calendar[Date], one active relationship, no ambiguity.
For your case, June 15-July 14 becomes 30 rows, each with $3.33/day, June gets 16 of those rows, July gets 14.
Once that's built, your measures are straightforward, standard SUM for Selected Month Total, TOTALYTD for YTD, SAMEPERIODLASTYEAR for % change, and average daily rate is just Amount/Days for the selected period.
💡 Helpful? Give a Kudos 👍 — keep the community growing.
✅ Solved your issue? Mark this as the Accepted Solution ✔️
Best regards,
Prince Singh | Data Science & Microsoft Fabric EnthusiastHi Prince,
Thank you for your response. I have used the below DAX
Allocated daily Charges =SUMX('UtilityAppended',VAR StartDate = 'UtilityAppended'[read_from]VAR EndDate = 'MLGW Appended'[read_to]VAR DaysOfService = 'UtilityAppended'[days_of_service]VAR DailyRate = DIVIDE('UtilityAppended'[amount], DaysOfService)VAR DaysInSelectedPeriod =COUNTROWS(FILTER(VALUES(Calendar[Date]),Calendar[Date] >= StartDate &&Calendar[Date] <= EndDate))RETURNDailyRate * DaysInSelectedPeriod)I also changed the existing active relationship between the Calendar table and the Utility table to inactive.
With this approach, do I need to use this new measure for all calculations that rely on the Calendar table in DAX? Will it work correctly with all report filters and slicers?
Additionally, does having a large number of rows in Power Query impact dashboard performance? If so, are there any best practices I should follow to optimize performance?
Hi arthiannadi06,
I would avoid trying to solve this with a dynamic relationship to Read To. The problem is that one bill can cover more than one month, so Read To can’t really represent the period that the charge belongs to.
For example, with a $100 bill from June 15 to July 14, you could split the amount based on the actual service days. That would give you roughly $53.33 for June and $46.67 for July.
I’d probably create an allocation table for this. It could be at daily level if you need daily reporting, or monthly level if your reporting is mainly monthly.
For monthly reporting, the table could simply look like:
Building A | Bill 123 | June 2026 | 53.33
Building A | Bill 123 | July 2026 | 46.67Then your model can stay pretty simple:
Calendar → Utility Allocation ← Building
Calendar filters the allocation table by the allocated month/date, and Building filters it by building.
Your main measure can then just be:
Total Utility =
SUM(Utility Allocation[Allocated Amount])From there, YTD and YoY measures are much easier to build because you're working with a normal date relationship.
I would definitely include Building in the allocation table. If you want to select a building and a month and see the corresponding utility cost, the building needs to be part of the fact/allocation data or connected through a proper Building dimension.
For a large dataset, I'd also try to do the allocation upstream in SQL, Power Query/Dataflow, Fabric, etc., rather than expanding everything with DAX. If you don't actually need daily reporting, I'd seriously consider the monthly allocation approach because it will keep the table much smaller.
So in your case, I'd probably go with:
DimDate
DimBuilding
DimUtility
↓
FactUtilityAllocationand have the allocation table contain the Bill ID, Building, Utility Type, Month/Date and Allocated Amount.
That should handle the monthly total, YTD, prior-year %, average daily rate and building-level reporting without needing inactive/dynamic date relationships.
Thankyou Praful_Potphode , danextian , Ashish_Mathur , ShahRukhSameer for Addressing the issue.
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
Regards,
Chaithanya
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
Regards,
Chaithanya
