topic Re: fill in future months null values with previous 3 months averages in DAX Commands and Tips

fill in future months null values with previous 3 months averages

Anonymous — Fri, 02 Jun 2023 18:56:50 GMT

Hello,

am looking to fill in future null values with last 3 months averages using DAX measure.

Tried the power query solution but it appears complicated to apply. Please see below data sample screenshot for illustration.

NULLs are captured for June data onward:

Thanks,

Re: fill in future months null values with previous 3 months averages

some_bih — Sun, 04 Jun 2023 20:17:50 GMT

Hi Anonymous please provide sample data with expected output for example

Re: fill in future months null values with previous 3 months averages

Anonymous — Mon, 05 Jun 2023 18:48:47 GMT

Hello,

Here is a data sample (below) of what am trying to accomplish in PBI DAX (Column 4).

Team	Month	support_hrs	Forecasted (support_hrs)
TEAM A	1/1/2022	6.17	Actual
TEAM A	2/1/2022	35.65	Actual
TEAM A	3/1/2022	26.2	Actual
TEAM A	4/1/2022	12.05	Actual
TEAM A	5/1/2022	32.22	Actual
TEAM A	6/1/2022	78.57	Actual
TEAM A	7/1/2022	30.75	Actual
TEAM A	8/1/2022	63.81	Actual
TEAM A	9/1/2022	8.63	Actual
TEAM A	10/1/2022	44.14	Actual
TEAM A	11/1/2022	72.14	Actual
TEAM A	12/1/2022	42.43	Actual
TEAM A	1/1/2023	28.66	Actual
TEAM A	2/1/2023	37.55	Actual
TEAM A	3/1/2023	51.69	Actual
TEAM A	4/1/2023	59.22	Actual
TEAM A	5/1/2023	36.59	Actual
TEAM A	6/1/2023	20.25	Actual
TEAM A	7/1/2023	0	= Ave of(June, May, Apr)
TEAM A	8/1/2023	0	= Ave of (Jul,Jun,May)
TEAM A	9/1/2023	0	= Ave of (Aug, Jul,Jun)
TEAM A	10/1/2023	0	= Ave of (Spe, Aug, Jul)
TEAM A	11/1/2023	0	= Ave of (Oct, Sep, Aug)
TEAM A	12/1/2023	0	= Ave of (Nov, Oct, Sep)

Please let me know if you need other details.

Thanks,

MMB

Re: fill in future months null values with previous 3 months averages

some_bih — Tue, 06 Jun 2023 08:43:26 GMT

Hi Anonymous I understand that you want to see rolling average. If yes, then please create two measure and in Date table create Year month column like

"Year month", FORMAT ( [Date], "YYYY-MM"

1. Simple sum, M_supp_hrs = suM(Sheet14[support_hrs])

Sup Rolling Average 3 Month =

CALCULATE (

AVERAGEX ( VALUES ( 'Date'[Year month]), [M_supp_hrs] ),

DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), -3, MONTH )

)

This newly created column should be used to see rolling average. I hope this help

Please how average calculation treat zero amount and how this affect your expected results.