topic Re: Calculate average rate on days between dates in DAX Commands and Tips

Calculate average rate on days between dates

Anonymous — Mon, 20 Mar 2023 09:33:09 GMT

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

Re: Calculate average rate on days between dates

olgad — Mon, 20 Mar 2023 09:43:49 GMT

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/?trackingId=bTPD6jERQXW4cwFZg%2FhJ0A%3D%3D

Re: Calculate average rate on days between dates

Anonymous — Mon, 20 Mar 2023 10:33:57 GMT

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!

Re: Calculate average rate on days between dates

olgad — Mon, 20 Mar 2023 10:45:43 GMT

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:

Re: Calculate average rate on days between dates

Anonymous — Mon, 20 Mar 2023 12:05:43 GMT

Hi olgad Thanks very much! We are going to test this then hopefully we can Accept Solution. Thanks v much for your help. Thanks