Calculated table or column with a rolling average

[ChristyQV]

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_WP01_WW01	21.2		18.07	>15
4/30/2021	BN_WP01_WW01	3.4		15.07	>15	2
5/1/2021	BN_WP01_WW01	8.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]

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]

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 · Accepted solution]

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

[ChristyQV]

Works like a charm and is easier than the path that I was on.  Thanks so much.

[Ashish_Mathur]

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]

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]

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]

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.