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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper 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
    Icon for Super User rankSuper 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.