Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
I have a table called “Rates”, which contain rows of rental rates for specific sets of dates. Each row has a column for the “Nightly rate” as well as the “dates From” and “Dates To”.
This is a basic example.
ID | Nightly rate | Dates from | Dates to | Nights |
1 | 100.00 | 01/01/2023 | 10/01/2023 | 9 |
2 | 150.00 | 12/01/2023 | 18/01/2022 | 6 |
3 | 300.00 | 02/01/2023 | 03/01/2023 | 1 |
4 | 200.00 | 07/02/2023 | 14/02/2023 | 7 |
I want to be able to calculate an average nightly rate from all the rates based on the date stayed. For example, row 1 would need transforming into a format where for each date between 01/01/2023 and 10/01/2023 carried a rate £100, and so on for all rows calculating the average rate for each individual date.
From the example table above, I'd like to be able to create another table or calculation that returns a row for each date between the 'Dates from' and Dates to' columns with the rate that applied. So using ID 1 as an example it would look lkit this.
Date | Rate | ID |
01/01/23 | 100 | 1 |
02/01/23 | 100 | 1 |
03/01/23 | 100 | 1 |
04/01/23 | 100 | 1 |
05/01/23 | 100 | 1 |
06/01/23 | 100 | 1 |
07/01/23 | 100 | 1 |
08/01/23 | 100 | 1 |
09/01/23 | 100 | 1 |
It would do this for all rates, and I could then link the date from my new tabe to my date table and calculate an average of all rates that applied on each date.
I hope that helps to better explain what I'm looking to achieve. Maybe the solution is not quite how I picture it, so open to any suggestions! @tamerj1 @FreemanZ @FreemanZ @amitchandak @andhiii079845 @Greg_Deckler
Hi, create another custom column for the previous date of the Date To column
Date.AddDays([Date To Column],-1)
and then plug it into the function that i shared with you.
the function creates numbers per row.
from this table
So, it shall work as you expect.
to this table:
Hi @olgad Thanks very much! We are going to test this then hopefully we can Accept Solution. Thanks v much for your help. Thanks
Hi, add custom column {Number.From([Dates from])..Number.From([Dates to]) }
Expand the List, turn to date
Here is the article that explains different scenarios
https://www.linkedin.com/pulse/hr-reporting-generating-records-between-start-end-values-dontsova/?tr...
Thanks very much! @olgad Two quick questions:
1) Would your solution work for multiple overlapping date ranges in the first table, it's difficult to tell from your example. For example:
ID | Nightly rate | Dates from | Dates to | Nights |
1 | 100.00 | 01/01/2023 | 10/01/2023 | 9 |
2 | 150.00 | 12/01/2023 | 18/01/2022 | 6 |
3 | 300.00 | 02/01/2023 | 03/01/2023 | 1 |
This is becasue we need to be able to average the nightly rates on specific dates.
2) These are check-in and check-out dates for stays in hotels. So therefore the rate does not apply to the last day in the range. e.g. ID 1 from the above example needs to look like this in the Output table:
Date | Rate | ID |
01/01/2023 | 100 | 1 |
02/01/2023 | 100 | 1 |
03/01/2023 | 100 | 1 |
04/01/2023 | 100 | 1 |
05/01/2023 | 100 | 1 |
06/01/2023 | 100 | 1 |
07/01/2023 | 100 | 1 |
08/01/2023 | 100 | 1 |
09/01/2023 | 100 | 1 |
ie. the last date in the output table needs to be the 9th, not 10th, as rate does not apply on the check out date.
Thanks for your help!
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
10 | |
8 | |
8 | |
8 | |
6 |
User | Count |
---|---|
14 | |
12 | |
11 | |
10 | |
9 |