topic Re: Averages to populate in missing dates in DAX Commands and Tips

Averages to populate in missing dates

Bryanna — Fri, 16 Jun 2023 18:05:12 GMT

Hi,

I have a table where i only have about 1 months worth of data at any given time. I would like to take the average of these values and have the average populate for Jan 2023 to current date aka where my actual data ends.

I calculated the average using the measure below but as you can see in my table it still only populates for the dates that I currently have. I would like the 5.38 to show for Jan 1st, Jan 2nd, Jan 3rd and so on until we hit May 15th where it will populate the actual value. Any thoughts?

Measure 2 =

AVERAGEX(

VALUES('Calendar Table'[Date]),

[Manual Appts Calc])

Thank you!

Re: Averages to populate in missing dates

tamerj1 — Sat, 17 Jun 2023 08:54:36 GMT

Hi Bryanna

please try

Measure2 =
COALESCE (
[Manual Appts Calc],
AVERAGEX ( ALLSELECTED ( 'Calendar Table'[Date] ), [Manual Appts Calc] )
)

Re: Averages to populate in missing dates

Bryanna — Mon, 19 Jun 2023 14:12:25 GMT

Thanks that worked! How can i now get the sum of these numbers? When its at the day level it populates the average for each day but if i were to change this at a month level its still just taking the average for that day and not summing all of the days in that month.

Re: Averages to populate in missing dates

tamerj1 — Mon, 19 Jun 2023 14:22:36 GMT

Bryanna

Please try

Measure2 =
SUMX (
VALUES ( 'Calendar Table'[Date] ),
COALESCE (
[Manual Appts Calc],
AVERAGEX ( ALLSELECTED ( 'Calendar Table'[Date] ), [Manual Appts Calc] )
)
)

Re: Averages to populate in missing dates

Bryanna — Mon, 19 Jun 2023 14:31:44 GMT

Thank you! So looks like it summed correctly, however now the values (averages) are missing on the days where they were populating previously.

Measure 3 is the original measure you provided and test manual inbounds is the new one trying to sum all of these values together. My goal is to have values populate for every date and the total be the sum of all of these together.

Re: Averages to populate in missing dates

tamerj1 — Mon, 19 Jun 2023 15:42:55 GMT

Bryanna

I expected that. It's ALLSELECTED that is doing the stange things 😅

Please try

Measure2 =
VAR AverageValue =
AVERAGEX ( ALLSELECTED ( 'Calendar Table'[Date] ), [Manual Appts Calc] )
RETURN
SUMX (
VALUES ( 'Calendar Table'[Date] ),
COALESCE ( [Manual Appts Calc], AverageValue )
)

Re: Averages to populate in missing dates

Bryanna — Mon, 19 Jun 2023 16:09:50 GMT

Yay, that worked!! thank you so much!