Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |