I am trying to create a daily average in a matrix where the denominator dynamically updates to the number of days in the month based on the filter context of that row's day. So, in the matrix below, there would be a daily average column at the end that would be different each day based on the previous day's billings and an additional day being added to the denominator.
The calculation would reset at the beginning of each month. I think it is similar to the following. Obviously, this is a rolling 14 day average.
Daily Average 2 = calculate(
'Billing Stats Productivity AZB'[Billing Total],
DATESINPERIOD('Calendar'[Date],LASTDATE('Calendar'[Date]),14,DAY)
)/14
1 ACCEPTED SOLUTION
I'd recommend using AVERAGE or AVERAGEX instead of trying to calculate the denominator.
For example, this averages over each date in the current month up to the current one:
MTD Daily Average =
AVERAGEX ( DATESMTD ( 'Calendar'[Date] ), [Billing Total] )
I'd recommend using AVERAGE or AVERAGEX instead of trying to calculate the denominator.
For example, this averages over each date in the current month up to the current one:
MTD Daily Average =
AVERAGEX ( DATESMTD ( 'Calendar'[Date] ), [Billing Total] )
