Calculating rates between date ranges

[jake4001]

Hi everyone,

I have a dataset showing items in storage between 2 dates and the charges accumulated for them. The charges occur daily: for example for id 1, we paid $25 on 8/30/2023, $25 on 8/31/2023, $25 on 9/1/2023, etc. I want to be able to calculate total rates for any time frame (i.e. by week / month / year - see desired result table). For id 1, $50 would go towards August 2023 and $75 would go towards September 2023. How could I do this in Power BI Desktop? Please share any DAX code as well. Thank you!

current table

id	shipdate	departdate	calendardays	dailyrate	totalrate
1	8/30/2023	9/3/2023	5	$25	$125
2	7/26/2023	7/30/2023	5	$20	$100
3	6/26/2023	7/4/2023	10	$30	$300

desired result

Time Period	BilledDays	TotalRate
Jun-23	5	$150
Jul-23	10	$250
Aug-23	2	$50
Sep-23	3	$75

[wdx223_Daniel]

create a date table

TotalRates = SUMX('YourTable','YourTable'[dailyrate]*COUNTROWS(FILTER(Dates,'Dates'[Date]>='YourTable'[shipdate]&&Dates[Date]<='YourTable'[departdate])))

BillDays = SUMX('YourTable',COUNTROWS(FILTER(Dates,'Dates'[Date]>='YourTable'[shipdate]&&Dates[Date]<='YourTable'[departdate])))