Forum Discussion

chrishnrh57's avatar
chrishnrh57
Regular Visitor
6 years ago
Solved

Rolling Average Excluding Zero

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:
 
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.
Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    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]

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    6 years ago

    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.