Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
GA1993
Helper II
Helper II

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.

 

GA1993_0-1633686973773.png

 

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.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hello,

 

 

I created a sample power bi for you, which contains my solution. If you did not update PBI, you will not be able to open it.

https://drive.google.com/file/d/1cU2fCXboj5cfFwcZNeTkpSwTKuB3PJ-K/view?usp=sharing

 

To give some context, I tried to break your calculations into multiple steps:

1. You need the Total Hire Time for the last 12 Months

2. This sum must be divided to 12 - N (where N = the blanks) - I assumed you wanted to divide the R12M Time to Hire to the number of months which are not blank

 

Solution:

3. Final Measure =
 
VAR R12MTotalTimetoHire =
CALCULATE (
SUM ( Table1[Time to Hire] ),
DATESINPERIOD ( Table1[Date], MAX ( Table1[Date] ), -12, MONTH )
)
VAR BlankMonths =
CALCULATE (
DISTINCTCOUNT ( Table1[Date] ),
Table1[Time to Hire] = 0
&& Table1[Time to Hire] = BLANK (),
DATESINPERIOD ( Table1[Date], MAX ( Table1[Date] ), -12, MONTH )
)
RETURN
R12MTotalTimetoHire / ( 12 - BlankMonths )
 
Look at 12/1/2021 from the below screenshot. The R12M time to hire is 41 (0+5+9+...+10). Then, 41 is divided to 12-5, where 5 represents the number of BLANK months within this time period (1/1/2021 - 12/1/2021). The average is going to be 41/7 = 5.85. 
 
DianaFM_0-1633697034530.png

If this is not the outcome you were hoping for, please share the raw data and some expected results in order to better help you.

 

Thanks.

 

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

Hello,

 

 

I created a sample power bi for you, which contains my solution. If you did not update PBI, you will not be able to open it.

https://drive.google.com/file/d/1cU2fCXboj5cfFwcZNeTkpSwTKuB3PJ-K/view?usp=sharing

 

To give some context, I tried to break your calculations into multiple steps:

1. You need the Total Hire Time for the last 12 Months

2. This sum must be divided to 12 - N (where N = the blanks) - I assumed you wanted to divide the R12M Time to Hire to the number of months which are not blank

 

Solution:

3. Final Measure =
 
VAR R12MTotalTimetoHire =
CALCULATE (
SUM ( Table1[Time to Hire] ),
DATESINPERIOD ( Table1[Date], MAX ( Table1[Date] ), -12, MONTH )
)
VAR BlankMonths =
CALCULATE (
DISTINCTCOUNT ( Table1[Date] ),
Table1[Time to Hire] = 0
&& Table1[Time to Hire] = BLANK (),
DATESINPERIOD ( Table1[Date], MAX ( Table1[Date] ), -12, MONTH )
)
RETURN
R12MTotalTimetoHire / ( 12 - BlankMonths )
 
Look at 12/1/2021 from the below screenshot. The R12M time to hire is 41 (0+5+9+...+10). Then, 41 is divided to 12-5, where 5 represents the number of BLANK months within this time period (1/1/2021 - 12/1/2021). The average is going to be 41/7 = 5.85. 
 
DianaFM_0-1633697034530.png

If this is not the outcome you were hoping for, please share the raw data and some expected results in order to better help you.

 

Thanks.

 

It totally worked! Thank you!

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors