Average ED Attendance Calculation for a specific selected month over the last three years

[Sonya_Zam]

Hi all,

I'm trying to figure out how to calculate  the average of ED attendances resulting from injury for a specific selected month over the last three years. In the Emergency Department dataset, there is a variable called "injury" which is a sum variable that includes values of 0 and 1. A value of 1 indicates that the ED attendance was due to an injury.  

The Emergency Department dataset (presentation_date) has a relationship with the Date (Date) dimension table, which also include month, year, and week.

To clarify, when I select the year 2022 and the month of December, I am hoping to obtain the average ED attendance resulting from injury for that specific month over the last three years. For example, the average ED attendances for the month of December would be 200 over the last three years.

Could you please help me out with this? What's the DAX formula that I can use to get this calculation done?

[Sonya_Zam]

Many thanks for your reply.

Yes- my table look like the above. The date_attedance in the Emergency Department table has a relationship with the date in the date dimension table.

I want to have a slicer that includes the month and year. For example, if I select Feb 2022, I should get the average of the injury attendances of the past three years for the month chosen which is the average of the sum of the month Feb 2022, Feb 2021 and Feb 2020 injury attendance.

So let's say injury attendance for Feb 2022 is 10, for Feb 2021 is 15 and for Feb 2020 is 7. So if we select Feb 2022 (from the slicer from the date dimension table), the average for that month for the past three years will be (10+15+7)/3 =10.6 injury attendances.

[FreemanZ]

hi @Sonya_Zam 

not sure if fully i get you, supposing you have a table like this:

1) try to add slicer visual with the only column from a calculated table like:

Slicer = 
SELECTCOLUMNS(
    {202211, 202212},
    "YYYYMM", [Value]
)

2) plot a measure with such code:

3YAvg = 
VAR _ym = SELECTEDVALUE(Slicer[YYYYMM])
RETURN
DIVIDE(
    CALCULATE(
        SUM(TableName[Injury]),
        VALUE(FORMAT(TableName[Date], "YYYYMM")) IN {_ym, _ym-100, _ym-200}
    ),
    3
)

it worked like:

[Sonya_Zam]

Please see my reply in the above. As the current dax forumula don't calculate what I am after. 

Many thanks in adavnce