HELP PLS! Dates between code in order to arrive at an daily average.

[Anonymous]

Hi I am a complete novice on PowerBI and would really appreciate some support with some coding.

I need some code which will enable me to return the daily rate for a service between the dates that the service is used.

In excel it is essentially a nested if statement which identifies whether a date is in the range within which that particular transaction was delivered and return the daily price against each of those days (except the last one), as below:

I need the DAX code to do the same but to create a separate row for each day of the transaction, (in the above example - a row for each day from 4th Jan to the 14th inclusive). Each row would need to replicate the same fields in each row for each of the day of the service - they would be things like country, city, client, product name etc etc...and most importantly the daily rate.

I would really appreciate someone getting back to me asap if they have been able to do this or if they have any suggestions at all!! Many Thanks!

[Manoj_Nair]

@Anonymous oops missed the pbix file link for reference purpose.

Dates between code in order to arrive at an daily average.pbix 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution and give it a thumbs up  
 
Best regards
Manoj Nair
Linkedin - https://www.linkedin.com/in/manoj-nair-%E2%98%81-344666104/

[Anonymous]

Hello @Manoj_Nair Sorry I missed this solution. Thankyou very much! I am new to the Forum so didn't see that the responses would be in messages. Apologies. I can't seem to access the pbix file link. Will it have expired? Could you resend at all pls? Many Thanks

[Manoj_Nair]

@Anonymous - try this DAX with sample datasets and solution.

 

[Anonymous]

Hi @Manoj_Nair One question I have is, in the new data table would overlapping stays be included allowing an average to be taken of all rates against a certain date? 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

For example, the highlighted date ranges in ID 1 and 3 have some degree of overlap as ID 3 happens on some of the dates which are also in ID 1, so the Output table would need to produce a column for each date but rows with each of the rate which applies from each data range, as we need to take an average of the nightly rate for each date. Sample output table below.

ID	01/01/2023	02/01/2023	03/01/2023	04/01/2023	05/01/2023	06/01/2023	07/01/2023	08/01/2023	09/01/2023	10/01/2023	11/01/2023	12/01/2023	13/01/2023	14/01/2023	15/01/2023	16/01/2023	17/01/2023	18/01/2023
1	100	100	100	100	100	100	100	100	100
2												150	150	150	150	150	150
3		300

I hope this makes sense? Thanks very much for your help!

[Greg_Deckler]

@Anonymous Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.