Add monthly average to a daily visual

[Anonymous]

Hi

I need to add data labels with monthly average to this serie. The visual represents daily values but my client wants only the monthly ones on it. Do you have any idea?

Regards

[Anonymous]

You may need a measure for this, like :

# Orders last 30 days =
CALCULATE (
    COUNT ( 'Orders'[OrderKey] ) / 30;
    USERELATIONSHIP ( 'Date'[Date]; 'Orders'[Date] );
    DATESINPERIOD ( 'Date'[Date]; LASTDATE ( 'Orders'[Date] ); -30; DAY )

[Anonymous]

Sorry @Anonymous, but... what happens with 28, 29 and 31 days months?

Thanks

[Anonymous]

It was an example how to calculate average over x days. For months you can use something like:

AvgLast1Month = CALCULATE(AVERAGE(Orders[amount]),DATESINPERIOD(Orders[Date],LASTDATE(Orders[Date]),-1,MONTH))/1

AvgLast3Months = CALCULATE(AVERAGE(Orders[amount]),DATESINPERIOD(Orders[Date],LASTDATE(Orders[Date]),-3,MONTH))/3

etc.

Hope this will help you.

Kind regards

[v-chuncz-msft]

@Anonymous,

You may use date hierarchy and take advantage of drill down.

https://docs.microsoft.com/en-us/power-bi/guided-learning/visualizations#step-18

[Anonymous]

Sorry @v-chuncz-msft, but I think thast this solution doesn't fit. It will show monthly serie with monthly average values. I need dairy serie with monthly average values.

Thanks anyway.