Rolling Average of Measure

[billuran2009]

HI I need to do rolling average of a measure called Enrollment:

Enrollment = CALCULATE(COUNTx(FILTER(PatientDetails,PatientDetails[Admit Date]<=min('DateTable'[Date]) && (ISBLANK(PatientDetails[DisDate]) || PatientDetails[DisDate]>min('DateTable'[Date]))),(PatientDetails[ACCOUNT NUMBER])),CROSSFILTER(PatientDetails[Admit Date],'DateTable'[Date],None))

Basically takes the active patients at the beginning of the month and I show it by month. 

How can I show the last 6 month average of the above measure? I have a date field called moyear 1/1/2022 to use aswell.

[v-rzhou-msft]

Hi @billuran2009 ,

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file without sensitive data.

Best Regards,

Rico Zhou

[v-rzhou-msft]

Hi @billuran2009 ,

According to your statement I know that you want to calculate last 6 month rolling average based on [Enrollment].

My Sample:

Try this code.

Last 6 Month Rolling Average of Enrollment = 
AVERAGEX (
    DATESINPERIOD ( 'Date'[Date], MIN ( 'Date'[Date] ), -6, MONTH ),
    [Enrollment]
)

Your measure is complex that it will use relationships and filter based on different tables. If this reply still couldn't help you solve your problem, please share a sample file without sensitive data with us and show us a screenshot with the result you want. This will make us easier to solve your problem.

Best Regards,
Rico Zhou

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

[parry2k]

@billuran2009 not sure if you saw this article, it will help answer your question Rolling 12 Months Average in DAX - SQLBI

✨ Follow us on LinkedIn and  to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.