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,

 

Please refer to the attached pbix file.

 

https://eygds-my.sharepoint.com/:u:/g/personal/nandukrishna_eygds_onmicrosoft_com/EW0RrJxyBlFLq_ETGrKKwLIB_Y5eSeQmq0-Ht_7sO01NTQ?e=XhEpiE 

 

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

 

edavilaamerivet's avatar
edavilaamerivet
Frequent Visitor
6 years ago

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 

 

 

  • nandukrishnavs's avatar
    nandukrishnavs
    Community Champion
    6 years ago

    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