DAX Average of a Moving Range

[andrew_kent]

Hi all,

I'm new to the forum but have been usng Power BI and DAX for a while, and was hoping to expand my knowledge! I'm basically trying to create an average of a moving range, which is essentially an average of the outcome of a measure that has been applied month on month if that makes sense?

I have a table of data with three values, the patients, patients who have a worked cardiac arrest and the month. I first create a measure to work out the worked cardiac arrest percentage;

worked_arrest_percentage:=SUM([worked_arrests]) / SUM([total_patients])

Then, I create a moving range, which is the variance of worked arrest percentage from one month to the next;

moving_range:=

VAR previous_month = 
CALCULATE([worked_arrest_percentage], 
ALL(tbl_worked_arrests_output[financial_year],
tbl_worked_arrests_output[incident_month (Month)]),
PREVIOUSMONTH(tbl_worked_arrests_output[incident_month]))
          				
VAR current_month = 
CALCULATE([worked_arrest_percentage],
ALL(tbl_worked_arrests_output[financial_year],
tbl_worked_arrests_output[incident_month (Month)]),
LASTDATE(tbl_worked_arrests_output[incident_month]))
          				
RETURN 
IF(ISBLANK(previous_month),0,ABS(previous_month - current_month))

The final piece of the puzzle is that I would like to create an average of the moving range which should give me something like this (in red is the measure I'm trying to create);

Which in simple terms is just AVERAGE([Moving Range]). However I know AVERAGE() gives errors in DAX when I try and do this. Does anyone have any suggestions?

Many thanks,

Andy

[amitchandak · Accepted solution]

@andrew_kent ,

calculate(Averagex('Date'[Mon Year]),[Moving Avg]), allselected())

Prefer date table for time intelligence.

or use you table and month year

[andrew_kent · Accepted solution]

A little bit of tweaking and it appears to be working;

moving_range_average:=CALCULATE(AVERAGEX(tbl_worked_arrests_output,[moving_range]), ALLSELECTED())

thank you again for your support.

Andy

[andrew_kent]

Thanks for your kind response however I don't see how this fits into my question;

- is the parenthesis in the right place as it looks like you have one too many?

- Prefer date table for time intelligence - do I need to restructure my data to achieve this or is this a nice to have?

- or use you table and month year - I'm not sure what you mean by this?

Many thanks,

Andy

[amitchandak · Accepted solution]

@andrew_kent ,

calculate(Averagex('Date'[Mon Year]),[Moving Avg]), allselected())

Prefer date table for time intelligence.

or use you table and month year