Static Average for a period of 8 weeks

[Anonymous]

Hello,

I am having an issue with creating a DAX measure that would calculate the static "average" of a column(Eg - quantity) for over a period of 8 weeks. I will be using this average value on a line over a bar chart. In my case, the average keeps changing every week, but when I put the avg value in a card it gives me a single value which I want to reproduce in my bar chart. 

Would someone help me correcting the below measure that would give me a static average? I even tried including Allexcept(week) but that didn't work. 

Avg of last 8 weeks =

CALCULATE(Average(master_detail_new[qty]),FILTER(master_detail_new,master_detail_new[forecast_type]="Actual Processed" ),

DATESBETWEEN(master_detail_new[week],

DATE(2019,12,1),

DATE(2020,2,9)

)

)

Thanks!! DAX Query  

[amitchandak]

Ideally week should come from separate table all should be used on week table

CALCULATE(Average(master_detail_new[qty]),FILTER(master_detail_new,master_detail_new[forecast_type]="Actual Processed" ),
filter(all(master_detail_new),master_detail_new[week]>= DATE(2019,12,1) && master_detail_new[week]<=DATE(2020,2,9)
)
)

Or , if selected from UI

measure =
var _max = maxx(master_detail_new,master_detail_new[week])
var _min = _max-56
return
CALCULATE(Average(master_detail_new[qty]),FILTER(master_detail_new,master_detail_new[forecast_type]="Actual Processed" ),
filter(all(master_detail_new),master_detail_new[week]>= DATE(2019,12,1) && master_detail_new[week]<=DATE(2020,2,9)
)
)

[Anonymous]

Hi @amitchandak ,

Thanks for your response. I tried using your measure but, still getting the same rolling average.

Here is a screencast - https://www.screencast.com/t/xzwLoSsxwZ

In the screenshot I actually want it to be a straight line meaning a static average(sum 8 weeks qty/8). Hope this makes sense.

Thanks

[amitchandak]

@Anonymous , share current formula

 if you need more help make me @

Appreciate your Kudos.

[Anonymous]

@amitchandak , I've used the formula below that you provided. I am hoping this is not an out of the box functionality. 

Avg of last 8 weeks =

var _max = maxx(master_detail_new,master_detail_new[week])

var _min = _max-56

return

CALCULATE(Average(master_detail_new[qty]),FILTER(master_detail_new,master_detail_new[forecast_type]="Actual Processed" ),

filter(all(master_detail_new),master_detail_new[week]>= DATE(2019,12,1) && master_detail_new[week]<=DATE(2020,2,9)

)

)

Also, my request is very similar to - https://community.powerbi.com/t5/Desktop/Static-Totals-or-Averages/td-p/444353

[Anonymous]

Hi Community,

Anyone out there who can help me with a solution for this? 

Thank you in advance.

[v-xuding-msft]

Hi @Anonymous ,

Could you tell me if your problem has been solved? If it is, kindly mark the helpful answer as a solution if you feel that makes sense. Welcome to share your own solution. More people will benefit from the thread.

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-xuding-msft · Accepted solution]

Hi @Anonymous ,

Maybe you could try to create a constant line in the visual, like below.

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.