Rolling Average Excluding Zero

[chrishnrh57]

Hello,

I have looked through this forum quite a bit and i'm seeing solutions very similar to what i'm trying to figure out, but not exactly what I need.

I'm looking for a Rolling Average that EXCLUDES zero. My initial Equation Looks like this:

(5-2)Output 5 Day Average =

CALCULATE(

Sum('(5-2) Shift Turnover Data'[(5.2) 8 Hr. Output]),

DATESINPERIOD('(5-1) Shift Turnover Data'[Date],LASTDATE('(5-1) Shift Turnover Data'[Date]),5,DAY)

)

/

CALCULATE(

DISTINCTCOUNT('(5-1) Shift Turnover Data'[Date]),

DATESINPERIOD('(5-1) Shift Turnover Data'[Date],LASTDATE('(5-1) Shift Turnover Data'[Date]),5,DAY)

)

And it looks like this:

It looks like it's taking the blank days and including them in the average.

I also tried using the "New Quick Measure" feature but every time I did it would look like this:

(5.1) 8 H.r Output rolling average =

IF(

    ISFILTERED('(5-1) Shift Turnover Data'[Date]),

    ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),

    VAR __LAST_DATE = LASTDATE('(5-1) Shift Turnover Data'[Date].[Date])

    RETURN

        AVERAGEX(

            DATESBETWEEN(

                '(5-1) Shift Turnover Data'[Date].[Date],

                DATEADD(__LAST_DATE, -1, DAY),

                DATEADD(__LAST_DATE, 5, DAY)

            ),

            CALCULATE(SUM('(5-1) Shift Turnover Data'[(5.1) 8 H.r Output]))

        )

)

And I'm assuming the "ERROR" message is preventing it from working because every time i place it in a chart it errors out.

I also saw this website as a solution in a previous post:

https://www.wiseowl.co.uk/blog/s2477/moving-averages.htm

But this was only for monthly averages and i wanted rolling 5 day averages.

I'm still very new at this so any information on this would be GREATLY appreciated. Thank you.

[v-shex-msft · Accepted solution]

Hi @chrishnrh57,

It is hard to find out your table structure and test from your snapshot. Can you please share some dummy data with the same table structure? 

How to Get Your Question Answered Quickly 

In addition, I'd like to suggest you use date function to manually define filter range instead of using time intelligence functions. (they do not support custom)

Time Intelligence "The Hard Way" (TITHW) 

Regards,

Xiaoxin Sheng

[Ashish_Mathur]

Hi,

I don't think any software will force a value in a blank cell to compute an average.  Simplify your request by sharing a simple Excel file and show the expected result there.  Share the link from where i can download your PBI file.

[amitchandak]

Make it blank and try

  VAR __LAST_DATE = LASTDATE('(5-1) Shift Turnover Data'[Date])
    RETURN
        AVERAGEX(
            DATESBETWEEN(
                '(5-1) Shift Turnover Data'[Date],
                DATEADD(__LAST_DATE, -1, DAY),
                DATEADD(__LAST_DATE, 5, DAY)
            ),
            if(CALCULATE(SUM('(5-1) Shift Turnover Data'[(5.1) 8 H.r Output]))=0, blank(),CALCULATE(SUM('(5-1) Shift Turnover Data'[(5.1) 8 H.r Output])))
        )
)	

In case date do not include time do not use ,date -> Shift Turnover Data'[Date].[Date] only use Shift Turnover Data'[Date]