12-month rolling average without dividing the values that are blank.
Hi, My question is particularly with the 12-month rolling average. What I did was I utilized the 'Quick Measure' feature of PowerBI and selected the corresponding fields to be calculated for the 12-months. Below was the automatic formula created. RAve_Time to Hire = IF( ISFILTERED('Date'[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 = ENDOFMONTH('Date'[Date].[Date]) VAR __DATE_PERIOD = DATESBETWEEN( 'Date'[Date].[Date], STARTOFMONTH(DATEADD(__LAST_DATE, -12, MONTH)), __LAST_DATE ) RETURN AVERAGEX( CALCULATETABLE( SUMMARIZE( VALUES('Date'), 'Date'[Date].[Year], 'Date'[Date].[QuarterNo], 'Date'[Date].[Quarter], 'Date'[Date].[MonthNo], 'Date'[Date].[Month] ), __DATE_PERIOD ), CALCULATE(SUM('MP - Engine'[Time to Hire]), ALL('Date'[Date].[Day])) ) ) This totally worked for those that have values per month. However, the problem takes place when the months are blank or have no values. For example, the rolling average below should be 100%, but since it was counting those months that had no values/blank, it is getting different rolling average totals. How do I solve this? I am thinking of filtering or placing an IF statement where only those months that have values will be calculated as opposed to dividing it to 12. However, I am not sure how to add this to the code above.
