Forum Discussion
Dynamic date relationship & Measures for data modelling for a use case
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.
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.67
Then 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
↓
FactUtilityAllocation
and 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.