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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MMB920
Frequent Visitor

fill nulls with previous months averages

Hello community,

 

I have a problem where I want to fill future months (nulls) with the last 3 months averagrs.

 

here is a screenshot of my editor page for illustration:

MMB920_0-1685690042008.png

any thoughts on how to go around this problem?

 

Thank you,

M

1 ACCEPTED SOLUTION
v-stephen-msft
Community Support
Community Support

Hi @MMB920 ,

 

Here's an example for the measure fill nulls with previous months average:

Measure = var _maxdate=MAXX(FILTER(ALLSELECTED('Table'),[Value]<>BLANK()),[Date])
var _start=EOMONTH(_maxdate,-3)
var _end=EOMONTH(_maxdate,0)
return IF(SUM('Table'[Value])=BLANK(),DIVIDE(CALCULATE(SUM('Table'[Value]),FILTER(ALLSELECTED('Table'),[Date]>_start&&[Date]<=_end)),3),SUM('Table'[Value]))

vstephenmsft_0-1685945362257.png

The measure is calculated based on the maximum date in the 'Table' where the 'Value' column is not blank. The formula then calculates the start and end dates of a 3-month period ending with the maximum date. If the sum of the 'Value' column is blank, the formula calculates the average of the 'Value' column for the 3-month period. Otherwise, the formula returns the sum of the 'Value' column.

 

                                                                                                                                                         

Best Regards,

Stephen Tao

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

View solution in original post

7 REPLIES 7
v-stephen-msft
Community Support
Community Support

Hi @MMB920 ,

 

Here's an example for the measure fill nulls with previous months average:

Measure = var _maxdate=MAXX(FILTER(ALLSELECTED('Table'),[Value]<>BLANK()),[Date])
var _start=EOMONTH(_maxdate,-3)
var _end=EOMONTH(_maxdate,0)
return IF(SUM('Table'[Value])=BLANK(),DIVIDE(CALCULATE(SUM('Table'[Value]),FILTER(ALLSELECTED('Table'),[Date]>_start&&[Date]<=_end)),3),SUM('Table'[Value]))

vstephenmsft_0-1685945362257.png

The measure is calculated based on the maximum date in the 'Table' where the 'Value' column is not blank. The formula then calculates the start and end dates of a 3-month period ending with the maximum date. If the sum of the 'Value' column is blank, the formula calculates the average of the 'Value' column for the 3-month period. Otherwise, the formula returns the sum of the 'Value' column.

 

                                                                                                                                                         

Best Regards,

Stephen Tao

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

Hi @v-stephen-msft ,

 

Much appreciate your help here this actually worked.

One last step to make it perfect, I applied an IF statement to replace for values coming up as NULLs (future ones) while keeping actual as is.

My formula went as the following; avg_forecast = if([Measure_actual] = NULL, [Measure], [Measure_Actual]).

 

I was encountered with error stating 'NULL' is not supported in this context. Any input/suggestion to this?

 

Thank you,

MMB

Hi @MMB920 ,

 

Try using BLANK() instead of NULL.

avg_forecast = if([Measure_actual] = BLANK() , [Measure], [Measure_Actual]).

 

Best Regards,

Stephen Tao

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

ppm1
Solution Sage
Solution Sage

What do you plan to do with these columns once generated? Why not just calculate it with a measure so it is responsive to slicers and doesn't take up memory and/or increase refresh time?

 

Pat

Microsoft Employee
MMB920
Frequent Visitor

Hi Pat,

 

Appreciate putting the option. I would be interested to see a DAX solution, could you provide that?

 

Thanks,

M

AlienSx
Super User
Super User

<no dax>

Could we do a measure then?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors