Forum Discussion
Anonymous
6 years agoNot applicable
Measure loads slowly
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!
Anonymous 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)
4 Replies
- Greg_Deckler
Community Champion
Anonymous 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)- AnonymousNot applicable
Greg_Deckler Thank you!!
- Greg_Deckler
Community Champion
Anonymous - Curious, how much did that speed things up?