Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
edavilaamerivet
Frequent Visitor

Moving Average 5 days

‎03-10-2020 03:29 PM

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 average is way off, I usually see around 600-700 visits every day and my moving average if I don't select one location is way off

 

fist photo is location and second is one location only

 

thank you and hope to get an answer

 
 

moving.PNGmoving2.PNG

 

Message 1 of 8
1,166 Views
0
7 REPLIES 7
nandukrishnavs
Super User
Super User

‎03-11-2020 12:30 AM

Hi @edavilaamerivet,

 

Please refer to the attached pbix file.

 

https://eygds-my.sharepoint.com/:u:/g/personal/nandukrishna_eygds_onmicrosoft_com/EW0RrJxyBlFLq_ETGr... 

 

Here is the DAX measure for your reference.

 

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 your comments.

 

Regards,

Nandu Krishna

 


Regards,
Nandu Krishna

Message 6 of 8
1,128 Views
0
edavilaamerivet
Frequent Visitor
In response to nandukrishnavs

‎03-11-2020 03:18 PM

Nandukrishnavs

 

thanks for trying to help me and I was able to make your formula work and it now works as a group but now the issue is that the average is not working correctly as I need to exclude the Saturdays and Sundays and if I filter them they still be calculated

 

this is what I'm using

 

Moving_Average_5_days2 =
VAR result =
CALCULATE (
AVERAGEX (
SUMMARIZE(DIM_Date,DIM_Date[Date], "# Visits", SUM(FACT_ClientPatientActivity[PatientVisitCount] ) ),
[# Visits]
),
DATESINPERIOD(DIM_Date[Date], LASTDATE(DIM_Date[Date]), -5,DAY)
)
return
result
 

Thanks 

 

 

Message 7 of 8
1,102 Views
0
nandukrishnavs
Super User
Super User
In response to edavilaamerivet

‎03-11-2020 11:20 PM

Hi @edavilaamerivet,

 

Create a calculated column in the table Fact_ClientPatientActivity

 

 

Weekend =
VAR dayVal =
    WEEKDAY ( Fact_ClientPatientActivity[Date] )
VAR result =
    IF ( dayVal = 1 || dayVal = 7, TRUE (), FALSE () )
RETURN
    result

 

 

Then create DAX measure 

 

Moving_Average_5_days2 = 
VAR selectedweekday =
    CALCULATE ( WEEKDAY ( SELECTEDVALUE ( Fact_ClientPatientActivity[Date] ) ) )
VAR mvgDay =
    SWITCH ( selectedweekday, 1, 7, 2, 7, 3, 7, 4, 7, 5, 7, 6, 5, 7, 7 )
VAR result =
    CALCULATE (
        AVERAGEX (
            SUMMARIZE (
                FILTER (
                    Fact_ClientPatientActivity,
                    Fact_ClientPatientActivity[Weekend] = FALSE ()
                ),
                FACT_ClientPatientActivity[Date],
                "# Visits", SUM ( FACT_ClientPatientActivity[PatientVisitCount] )
            ),
            [# Visits]
        ),
        DATESINPERIOD (
            FACT_ClientPatientActivity[Date],
            LASTDATE ( FACT_ClientPatientActivity[Date] ),
            ( -1 * mvgDay ),
            DAY
        )
    )
RETURN
    result

 

 

Now you can apply a visual level/page level filter to exclude Saturday and Sunday. 

The measure Moving_Average_5_days2 is already excluding the weekends. 
 
Regards,
Nandu Krishna

 


Regards,
Nandu Krishna

Message 8 of 8
1,090 Views
0
v-diye-msft
Community Support
Community Support

‎03-10-2020 11:57 PM

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.

 

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
Message 4 of 8
1,134 Views
0
edavilaamerivet
Frequent Visitor
In response to v-diye-msft

‎03-11-2020 09:26 AM

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 

 

Message 5 of 8
1,110 Views
0
nandukrishnavs
Super User
Super User

‎03-10-2020 11:46 PM

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

 

 

 

snapshot1.JPGsnapshot2.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Please let me know if you are finding any issues.

 

Regards,

Nandu Krishna

 


Regards,
Nandu Krishna

Message 2 of 8
1,113 Views
0
edavilaamerivet
Frequent Visitor
In response to nandukrishnavs

‎03-11-2020 09:25 AM

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 

 

Message 3 of 8
1,111 Views
0

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

View All