cancel
Showing results for
Did you mean:
Frequent Visitor

Calculate Monthly Average for Date Range - need formula for denominator if months are blank

My formula below works properly for customers that have Revenue > 0 in all 11 months between Jan 1 2018 and Nov 30 2018.   I am dividing by 11 (highlighted in red below).

AvgRevenueJanNov  = divide(CALCULATE(sum(CustomerMargin[Revenue]), DATESBETWEEN(CustomerMargin[Date],DATE(2018,1,1),DATE(2018,11,30))),11,0)

However, the average is understated if a customer has Revenue = 0 in any of these months.

Any suggestions for how I can substitute a formula to divide by the number of months with Revenue > 0 instead of dividing by 11 (highlighted in red)?

1 ACCEPTED SOLUTION

Accepted Solutions
Established Member

Re: Calculate Monthly Average for Date Range - need formula for denominator if months are blank

Hello @eperry

You can use some variables and filters in your measure to get the count of the number of months where the revenue <> 0 for each customer.

```AvgRevenueJanNov =
VAR Jan = DATE ( 2018, 1, 1 )
VAR Nov = DATE ( 2018, 11, 30 )
VAR RowCount =
CALCULATE (
COUNTROWS ( VALUES ( CustomerMargin[Date] ) ),
DATESBETWEEN ( CustomerMargin[Date], Jan, Nov ),
CustomerMargin[Revenue] <> 0
)
RETURN
DIVIDE (
CALCULATE (
SUM ( CustomerMargin[Revenue] ),
DATESBETWEEN ( CustomerMargin[Date], Jan, Nov )
),
RowCount,
0
)```

2 REPLIES 2
Established Member

Re: Calculate Monthly Average for Date Range - need formula for denominator if months are blank

Hello @eperry

You can use some variables and filters in your measure to get the count of the number of months where the revenue <> 0 for each customer.

```AvgRevenueJanNov =
VAR Jan = DATE ( 2018, 1, 1 )
VAR Nov = DATE ( 2018, 11, 30 )
VAR RowCount =
CALCULATE (
COUNTROWS ( VALUES ( CustomerMargin[Date] ) ),
DATESBETWEEN ( CustomerMargin[Date], Jan, Nov ),
CustomerMargin[Revenue] <> 0
)
RETURN
DIVIDE (
CALCULATE (
SUM ( CustomerMargin[Revenue] ),
DATESBETWEEN ( CustomerMargin[Date], Jan, Nov )
),
RowCount,
0
)```

Frequent Visitor

Re: Calculate Monthly Average for Date Range - need formula for denominator if months are blank

Many thanks! Works like a Charm!