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...
Need Help
  • Anonymous's avatar
    Anonymous
    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
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]