Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
lherbert501
Helper V
Helper V

6 Month Average Overall without Slicer

Hi there,

 

I have a measure that calculates the rolling average over 6 months which works great as below.

 

Capture2.PNG

 

In my dataset it only contains 6 months so it will cut out e.g. February when August comes along.

 

I need to work out the overall 6 month average (which would be 650 in this case), but I still need to use the slicer for the current month. Is it possible to have a measure to work out the 6 month average overall but ignore the slicer within the measure?

 

This is what I have below but it isn't working,

 

R6 =
var NumOfMonths = 6
var LastSelectedDate = MAX(RS_Dates[DateFull])
var Period = DATESINPERIOD(RS_Dates[DateFull], LastSelectedDate,  -NumOfMonths,  MONTH)
Var Result =
CALCULATE(
AVERAGEX(
    VALUES(RS_Dates[PeriodCal]),
    [measure]), filter(RS_Dates, RS_Dates[PeriodCal] = LastSelectedDate)
return
Result

 

Turning off interactions wont work in this case as theres a measure on the same visual that requires the slicer.

 

 

 

Thanks in advance

 

Liam

 

 

 

1 ACCEPTED SOLUTION

Managed to do this with the ALL function 🙂

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

@lherbert501 , First of all, if you select data of month and you need value more than that, then you slicer on an independent date table 

 

date is joined and date 1 independent table

 

//Date1 is an independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = eomonth(_max, -6) +1
return
calculate( CALCULATE(AverageX(Values('Date'[MONTH Year]),calculate(Sum('Table'[Value)))
,DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-6,MONTH)) , filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))

 

 

 

hi @amitchandak 

 

Thanks so much for the reply. 

The new measure that you have wrote with an extra date table gives the same result as the rolling average below. 

The column in yellow is what I'm aiming for if its possible. 6 Month overall average regardless of slicer change?

 

Capture.PNG

 

Thanks

 

Liam

Managed to do this with the ALL function 🙂

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors