Forum Discussion

ChristyQV's avatar
ChristyQV
Frequent Visitor
5 years ago
Solved

Calculated table or column with a rolling average

Dear GURUS!  Please help!

I have a data set like below, with the result I'm working towards:

DateWellCodeRunHours Desired column- 3 Day Running AvgCriteriaResult
4/29/2021BN_WP01_WW0121.2 18.07>15 
4/30/2021BN_WP01_WW013.4 15.07>152
5/1/2021BN_WP01_WW018.1 10.90<15 

I would like to have a virtual table with three columns date, well code and the rolling 3 day average of the run hours.  I've used the below, but the value returned for "3DAY" is always the same as the original run hours.

 

 

 

 

CALCULATETABLE (
    ADDCOLUMNS (
        SUMMARIZE (
            FactPumpageDaily,
            FactPumpageDaily[Date],
            FactPumpageDaily[WellCode]
        ),
        "3DAY",
            CALCULATE (
                AVERAGE ( FactPumpageDaily[RunHours] ),
                DATESINPERIOD ( DimDates[Date], FactPumpageDaily[Date], -3, DAY )
            )
    )
)

 

 

 

 

I've tried with AverageX and I've also added ALL(DimDates[Date]) with the same results to clear the "context" of the date.

 

In the end I am trying to count the number of times the 3 day average exceeds a specific value as shown in my example above.  If I can get the 3day average into a table then I believe I can use a countrows and a filter to get what I am looking for.

 

Any guidance would be appreciated.

  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi ChristyQV ,

     

    Try using this formula to calculate the average value.

     

    Column = CALCULATE(AVERAGE('FactPumpageDaily'[RunHours]),FILTER(ALLEXCEPT('FactPumpageDaily','FactPumpageDaily'[WellCode]),'FactPumpageDaily'[Date]>EARLIER('FactPumpageDaily'[Date])-3&&'FactPumpageDaily'[Date]<=EARLIER('FactPumpageDaily'[Date])))

     

     

    Best Regards,

    Jay

8 Replies

  • Hi,

    Why are you writing a calculated Table formula?  Why are you not creating a visual and dragging measures to it?  Share a dataset, descibe your question and show the expected result. 

    • ChristyQV's avatar
      ChristyQV
      Frequent Visitor

      Thanks for helping.  The calculation for the rolling average works perfect if I place it in visual as you suggest.  I want to put the average into a table so that I can do other things with it, mainly figure out how many times it is greater than a specified value.  I've been experimenting with something like below.

      3daycount =
      COUNTROWS (
          FILTER (
              SUMMARIZE (
                  FactPumpageDaily,
                  FactPumpageDaily[Date],
                  FactPumpageDaily[WellCode],
                  "3DAY",
                      CALCULATE (
                          AVERAGEX ( FactPumpageDaily, FactPumpageDaily[RunHours] ),
                          DATESINPERIOD ( DimDates[Date], MAX ( DimDates[Date] ), -3, DAY )
                      )
              ),
              [3DAY] >= 15
          )
      )
      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User

        Hi,

        If you wish to "igure out how many times it is greater than a specified value", you can do so with a measure.  As requested in my previous message, share some data, describe the question and show th expected result.

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi ChristyQV ,

     

    If "Desired column- 3 Day Running Avg" is the result you want, I just can't figure out how did you get it by "RunHours". Please correct me If I misunderstand your meaning.

     

    Best regards,

    Jay

    • ChristyQV's avatar
      ChristyQV
      Frequent Visitor

      The running average is the average of the hours from the selected date and the preceding two days.  It works as a measure just fine as defined below.  (My sample data just has enough info to check for 5/1/2021.)  Ultimately, I am trying to count the number of times the average is >= 15.   The Calculatetable in my question is just my first step in that process.   After getting the CalculateTable/summarize to work, my intent is to wrap it in a CountRows, somthing like below

      3daycount =
      COUNTROWS (
          FILTER (
              SUMMARIZE (
                  FactPumpageDaily,
                  FactPumpageDaily[Date],
                  FactPumpageDaily[WellCode],
                  "3DAY",
                      CALCULATE (
                          AVERAGEX ( FactPumpageDaily, FactPumpageDaily[RunHours] ),
                          DATESINPERIOD ( DimDates[Date], MAX ( DimDates[Date] ), -3, DAY )
                      )
              ),
              [3DAY] >= 15
          )
      )

       

      When I use this, I always just get the count for the record date (which is 1 or 0), even when I add an All(DimDates[Date]) to the calculate to change the date context.

      • Anonymous's avatar
        Anonymous
        Not applicable

        Hi ChristyQV ,

         

        Try using this formula to calculate the average value.

         

        Column = CALCULATE(AVERAGE('FactPumpageDaily'[RunHours]),FILTER(ALLEXCEPT('FactPumpageDaily','FactPumpageDaily'[WellCode]),'FactPumpageDaily'[Date]>EARLIER('FactPumpageDaily'[Date])-3&&'FactPumpageDaily'[Date]<=EARLIER('FactPumpageDaily'[Date])))

         

         

        Best Regards,

        Jay