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.
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 Karmani