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 Community,
I built a power bi that includes a measure for a rolling 5-day average. This rolling average can only include volume from non-weekends and non-holidays. I've gotten it to calculate correctly using the dax below, but it takes a while to run. Can anyone help me write this more efficiently?
Also, the business days index ranks business days in ascending order from today. In other words, the most recent business day is ranked 1 (today). The second most recent is ranked 2 (yesterday). Days in the date table that aren't business days are left blank.
Rolling 5 day average_business days only =
VAR TopRank = min(DateDim[Business days index])
Var RankUL = TopRank+5
VAR RollingSum =
sumx(
filter(
ALLSELECTED('Visit Details'),
'Visit Details'[Business day index] < RankUL &&
'Visit Details'[Business day index] >= TopRank),
[Total Visits])
Return Divide(RollingSum,5)
Thank you in advance for your help!
Solved! Go to Solution.
@738o51 Maybe try:
Rolling 5 day average_business days only =
VAR TopRank = min(DateDim[Business days index])
Var RankUL = TopRank+5
VAR RollingSum =
CALCULATE
SUM([Total Visits]),
filter(
ALLSELECTED('Visit Details'),
'Visit Details'[Business day index] < RankUL &&
'Visit Details'[Business day index] >= TopRank),
)
Return Divide(RollingSum,5)
@738o51 Maybe try:
Rolling 5 day average_business days only =
VAR TopRank = min(DateDim[Business days index])
Var RankUL = TopRank+5
VAR RollingSum =
CALCULATE
SUM([Total Visits]),
filter(
ALLSELECTED('Visit Details'),
'Visit Details'[Business day index] < RankUL &&
'Visit Details'[Business day index] >= TopRank),
)
Return Divide(RollingSum,5)
@738o51 - Curious, how much did that speed things up?
My data set is pretty large so the change was significant. I went from about 2 minutes to load the graph to about 1 second. Thanks again!
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 |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |