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
nandukrishnavs's avatar
nandukrishnavs
Community Champion
6 years ago

Hi edavilaamerivet,

 

Sample Table 

 

DateLocationVisits
01-01-2020Trivandrum200
02-01-2020Trivandrum160
03-01-2020Trivandrum202
04-01-2020Trivandrum230
05-01-2020Trivandrum204
06-01-2020Trivandrum204
07-01-2020Trivandrum130
08-01-2020Trivandrum131
09-01-2020Trivandrum132
10-01-2020Trivandrum133
11-01-2020Trivandrum210
12-01-2020Trivandrum211
13-01-2020Trivandrum212
14-01-2020Trivandrum213
15-01-2020Trivandrum214
16-01-2020Trivandrum215
17-01-2020Trivandrum216
18-01-2020Trivandrum217
19-01-2020Trivandrum218
20-01-2020Trivandrum219
21-01-2020Trivandrum220
22-01-2020Trivandrum221
23-01-2020Trivandrum222
24-01-2020Trivandrum223
25-01-2020Trivandrum224
26-01-2020Trivandrum225
27-01-2020Trivandrum226
28-01-2020Trivandrum227
29-01-2020Trivandrum228
30-01-2020Trivandrum229
01-01-2020Kochi100
02-01-2020Kochi110
03-01-2020Kochi120
04-01-2020Kochi130
05-01-2020Kochi140
06-01-2020Kochi150
07-01-2020Kochi160
08-01-2020Kochi170
09-01-2020Kochi180
10-01-2020Kochi190
11-01-2020Kochi210
12-01-2020Kochi200
13-01-2020Kochi190
14-01-2020Kochi180
15-01-2020Kochi170
16-01-2020Kochi160
17-01-2020Kochi150
18-01-2020Kochi250
19-01-2020Kochi255
20-01-2020Kochi260
21-01-2020Kochi265
22-01-2020Kochi270
23-01-2020Kochi260
24-01-2020Kochi250
25-01-2020Kochi240
26-01-2020Kochi230
27-01-2020Kochi220
28-01-2020Kochi210
29-01-2020Kochi200
30-01-2020Kochi190

 

Please try the below DAX measure

 

 

Moving_Average_5_Days = 
VAR result =
    CALCULATE (
        AVERAGEX (
            SUMMARIZE ( 'Table', 'Table'[Date], "visit", SUM ( 'Table'[Visits] ) ),
            [visit]
        ),
        DATESINPERIOD ( 'Table'[Date], LASTDATE ( 'Table'[Date] ), -5, DAY )
    )
RETURN
    result

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Please let me know if you are finding any issues.

 

Regards,

Nandu Krishna

 

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