Average a measure across whole month and input for each date in month

[HFoy]

Hi All, 

I have a measure: 

fc_Delivery_Rate = CALCULATE([Actual_Delivery], SAMEPERIODLASTYEAR('Calendar'[Date]))

This gives me the delivery rate (actual delivery = what was actually delivered/  what was requested )  for the previous year for each date of 2019/20. I want to be able to take the average delivery across each month and apply that value to every date in the month. A short example is given in the image. I want to be able to create the Avg_Month_Delivery, any help would be apprecaited.

Thanks,

Hannah

[v-piga-msft]

Hi @HFoy ,

Have you solved your problem?

If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please share your data sample which could reproduce your scenario and your desired output so that we could help further on it.

Best Regards,

Cherry

[AlB · Accepted solution]

Hi @HFoy,

I'd need to see the relationships in your model and the structure of your tables to come up with an accurate answer. You should show that. Assuming the most typical structure, you could create a measure like this:

Measure =
AVERAGEX (
    ADDCOLUMNS (
        FILTER (
            ALL ( 'Calendar'[Date] ),
            YEAR ( 'Calendar'[Date] ) = YEAR ( SELECTEDVALUE ( Table1[Date] ) )
                && MONTH ( 'Calendar'[Date] ) = MONTH ( SELECTEDVALUE ( Table1[Date] ) )
        ),
        "_Day", [fc_Delivery_Rate]
    ),
    [_Day]
)

Please always show your sample data in text-tabular format in addition to (or instead of)  screen captures. That allows people trying to help to readily copy the data and run a quick test, plus it increases the likelihood of your question being answered. Just use 'Copy table' in Power BI and paste it here. Or, ideally, share the pbix (beware of confidential data).