cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
eperry Frequent Visitor
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

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

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
    )

 

 

Highlighted
eperry Frequent Visitor
Frequent Visitor

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

Many thanks! Works like a Charm!