Forum Discussion

OKgo's avatar
OKgo
Icon for Helper IV rankHelper IV
8 years ago
Solved

Moving average - considering zeros and blank entries

I am looking to alter three DAX formula so that the red boxes are not calcuated (blank) and the red-stars are calcuated as 90 and 90

Hours spent. = SUM(Table46[Hrs])
Number of preceding periods. = COUNTROWS(FILTER(CALCULATETABLE(SUMMARIZE('Calendar',[Year-month],"ABCD",[Hours spent.]),DATESBETWEEN('Calendar'[Date],EDATE(MIN('Calendar'[Date]),-2),min('Calendar'[Date])-1)),[ABCD]>0))
3 months rolling average. = if([Number of preceding periods.]=2,AVERAGEX(DATESBETWEEN('Calendar'[Date],EDATE(MIN('Calendar'[Date]),-2),MAX('Calendar'[Date])),[Hours spent.]),BLANK())

Need Help

7 Replies

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        8 years ago

        Hi,

         

        Is this your expected result?  You may download my file from here.  Here are the revised measures:

         

        Number of preceding periods.. = COUNTROWS(FILTER(CALCULATETABLE(SUMMARIZE('Calendar',[Year-month],"ABCD",[Hours spent..]),DATESBETWEEN('Calendar'[Date],EDATE(MIN('Calendar'[Date]),-2),min('Calendar'[Date])-1)),[ABCD]>=0))

         

        3 months rolling average.. = if(ISBLANK([Hours spent..]),BLANK(),if([Number of preceding periods..]=2,AVERAGEX(DATESBETWEEN('Calendar'[Date],EDATE(MIN('Calendar'[Date]),-2),MAX('Calendar'[Date])),[Hours spent..]),BLANK()))

         

        Select the visual, click to the Format button in the visualisation pane and swith the "Show on rows" button to On.

         

        Hope this helps.