Forum Discussion

edavilaamerivet's avatar
edavilaamerivet
Frequent Visitor
6 years ago

Moving Average 5 days

Hello community I'm looking to see if someone can help me as I'm trying to get a Moving average of 5 days and it seems to work the formula but once that I don't select a unique practice the moving a...
Need Help
Show and Tell
Tutorial Requests
v-diye-msft's avatar
v-diye-msft
Icon for Community Support rankCommunity Support
6 years ago

Hi edavilaamerivet 

 

you might consider creating pbix file that will contain some sample data (remove the confidential info), upload the pbix to onedrive or dropbox and share the link to the file. Please do not forget to describe the expected results based on this sample data.

 

 

edavilaamerivet's avatar
edavilaamerivet
Frequent Visitor
6 years ago

https://www.dropbox.com/s/xo2qhmqdsi2mznh/Moving%20Average-2.pbix?dl=0 

 

I can't seem to make the formula to work, I have attached a sample of the file with same columns names

 

Moving_Average_5_days2 =

VAR result =

CALCULATE (

    AVERAGEX (

        SUMMARIZE(DIM_Date, DIM_Date[Date], "Client Visit", SUM(FACT_ClientPatientActivity[ClientVisitCount] ) ),

        [Client Visit]

    ),

    DATESINPERIOD(DIM_Date[Date], LASTDATE(DIM_Date[Date]), -5,DAY)

)

return

result

 

the dim_date is my Calendar table

"Client visits" this is a calculated column from the sum(clientvisitcount)

Fact_clientpatientactivity is the table were the data for the visits are

 

also to point out is the State or location is in the Dim_installation table column State'

 

What I'm thinking the problem is that I'm trying to group the by state "AK" and formula is not at that level

 

not sure

 

Thanks