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 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

 
 

 

Need Help
Show and Tell
Tutorial Requests

7 Replies

  • 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 

       

  • v-diye-msft's avatar
    v-diye-msft
    Community 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 

       

  • 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