Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
Below is my data set and I'm trying to accomplish a dynamic average calculation. I need to come up with a DAX formula that will calculate dynamically every day the average [Rate] of the last 10 days:
How this can be done?
Thanks!
Nir H
Solved! Go to Solution.
Hi @nhol,
Could you please mark the proper answer as solution or share the solution if it's convenient for you? That will be a big help to the others.
Best Regards!
Dale
Hi Nir,
You can try it out in this file.
1. As a measure.
Measure = VAR currentDate = MIN ( 'Table1'[Date] ) VAR startDate = currentDate - 9 RETURN IF ( CALCULATE ( MIN ( 'Table1'[Date] ), ALL ( 'Table1' ) ) > startDate, BLANK (), AVERAGEX ( FILTER ( ALL ( 'Table1' ), 'Table1'[Date] <= currentDate && 'Table1'[Date] >= startDate ), 'Table1'[Rate] ) )
2. As a calculated column.
1)
AverageRate = VAR currentDate = 'Table1'[Date] VAR startDate = currentDate - 9 RETURN IF ( MIN ( 'Table1'[Date] ) > startDate, BLANK (), CALCULATE ( AVERAGE ( Table1[Rate] ), FILTER ( ALL ( 'Table1' ), 'Table1'[Date] <= currentDate && 'Table1'[Date] >= startDate ) ) )
Or 2)
AverageRate2 = VAR currentDate = 'Table1'[Date] VAR startDate = currentDate - 9 RETURN IF ( MIN ( 'Table1'[Date] ) > startDate, BLANK (), AVERAGEX ( FILTER ( 'Table1', 'Table1'[Date] <= currentDate && 'Table1'[Date] >= startDate ), 'Table1'[Rate] ) )
Best Regards,
Dale
Thank you so much Dale!
Actually this was perfect until I realized that my dataset of [Dates] skips weekends and only capture buisness days. So I don'y really have a full continuityit by dates and basically every 5 days it skips to the next week starting date. This also apears in holidays where I have no date because it is not a business day.
I like the way you approached it but the (-9) thing probably won't work in this case.
Any other option?
By the way, I used your first option as a column (2.1)
(The [Average Last 10 Business Days] is a calculation I did manually in my Excel sheet)
Thanks again!
Nir H
Hi @nhol,
Two questions.
1. Are there records for non-business day? For example, 5th November 2017 is Sunday, is there a record for this day?
2. 1 Nov - 12 Nov are only 8 workdays.
Best Regards,
Dale
1. Nope. there are no records for non-business days so basically the data is sequential (just skipping days where data=[blank]).
2. you are right my mockup is bad but you got the idea...
One other thing that I realized just recently is the fact that when I'm using DirectQuery (because I need real-time data) PowerBI limits me in implementing any data query and I have to do it in the database. Not sure why PowerBI has this limitation.
Thanks!
Nir H.
Hi Nir,
You can try a measure like this.
Measure = VAR currentDate = MIN ( 'Table1'[Date] ) VAR startDate = currentDate - 13 RETURN IF ( CALCULATE ( MIN ( 'Table1'[Date] ), ALL ( 'Table1' ) ) + 10 < currentDate, AVERAGEX ( FILTER ( ALL ( 'Table1' ), 'Table1'[Date] <= currentDate && 'Table1'[Date] >= startDate ), 'Table1'[value] ), BLANK () )
Best Regards,
Dale
Hi @nhol,
Could you please mark the proper answer as solution or share the solution if it's convenient for you? That will be a big help to the others.
Best Regards!
Dale
@v-jiascu-msft Hi Dale,
I have a similar question with Average.
My data table is as follows :
I would like to calculate the average utilisation of the team for day shift and afternoon shift on a daily basis.
As you can see, the day shift team consists of 3 people : Alice, Sera and Peter
Afternoon shift consists of 2 people: Tina and Vivianne
What I ideally want on my chart: example : On 1 Dec 21, on day shift, Average team Utilisation =(6%+8%+9%+6%)/3
And similarly, lets say if one person is away, Average team utilisation on Day shift= (Sum of utilisation)/2.
Please provide me assistance on the formula for this. Thanks.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |