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: Date WellCode RunHours   Desired column- 3 Day Running Avg Criteria Result 4/29/2021 BN_WP0...
Need Help
  • 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

Ashish_Mathur's avatar
Ashish_Mathur
Super User
5 years ago

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
    5 years ago

    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
      5 years ago

      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.

      • ChristyQV's avatar
        ChristyQV
        Frequent Visitor
        5 years ago

        I have a sample data with the result that I am trying to get to at the very beginning of the original question.  I apologize, but I do not understand what other data and expected result you require.   I have decided to have SQL Server do this calculation in a view and use this view in PBI so I will not waste anymore of your time.