Forum Discussion
Calculating rates between date ranges
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 |
1 Reply
- wdx223_Daniel
Community Champion
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])))
