Averages to populate in missing dates

[Bryanna]

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!

[Bryanna]

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.

[tamerj1]

@Bryanna 

Please try

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

[Bryanna]

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.

[tamerj1 · Accepted solution]

@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 )
)

[Bryanna]

Yay, that worked!! thank you so much!

[tamerj1]

Hi @Bryanna 

please try

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