Rolling Average with Filtered SUM

[Nagin]

Hi All,

I have the following measure which works fine when calculating the rolling average where [hospital_spell_duration_d] is >=1. However I am now trying to create a new measure to identify the rolling average SUM of [hospital_spell_duration_d] and where [hospital_spell_duration_d] is >=1

1+ LOS 3 Week Avg Bed Days =
VAR __LAST_DATE =
LASTDATE ( DIM_Date_Table[Date] )
VAR RollingAverage =
AVERAGEX (
DATESBETWEEN (
DIM_Date_Table[Date],
DATEADD ( __LAST_DATE, -20, DAY ),
__LAST_DATE
),
CALCULATE (
COUNT ( 'NEL Admissions'[Admission_Date_Hospital_Provider_Spell] ),
'NEL Admissions'[Hospital_Spell_Duration_D] >= 1
)
)
RETURN
IF ( __LAST_DATE > TODAY (), BLANK (), RollingAverage )

Thanks

Nagin

[v-zhangti · Accepted solution]

Hi, @Nagin 

Can you provide sample data for testing? Sensitive information can be removed in advance. What kind of expected results do you expect? You can also show it with pictures. I look forward to your response.

Best Regards,

Community Support Team _Charlotte

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

[FreemanZ]

Hi @Nagin  what do you expect to return specifically?