Moving average - considering zeros and blank entries

[OKgo]

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())

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[OKgo]

Thank you Ashish - I can't get the 3m average into the rows. They way you orignally had this looked really nice.

https://www.dropbox.com/sh/93zvi0qoytr1owv/AABDXX_CpkIB_EpRhLqiIBjfa?dl=0

[Ashish_Mathur]

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. 

[OKgo]

Thank you for sorting out the red boxes.

The new formula works great with zeros in the database. But not for when there are blanks. For example the 109 below should consider the blank (114 + 0 + 104) / 3 = 73

Can we have the first two months not populate averages like the old formula?

This is very education for me and thank you again for your help - if I can wire you a coffee via Paypal PM me 🙂

[Ashish_Mathur · Accepted solution]

Hi,

Hope this works.  Download the file from here.

[OKgo]

Hi Ashish - Thank you so much. Thats perfect. Loving the new months elapsed expression! Having it on the calendar table makes it all the more robust and reuseable

[Ashish_Mathur]

You are welcome.