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

[Anonymous]

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

[jdbuchanan71 · Accepted solution]

Hello @Anonymous 

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
    )

[Anonymous]

Many thanks! Works like a Charm!