3 Month Rolling Averages - How to exclude the days with no sales

[Anonymous]

Hi All 

How do I modify this formula so that we're only averaging over days that have sales. 

Average Sales 3 Month Rolling %:=
AVERAGEX(
DATESINPERIOD( 'Primary Date Table'[Date] , LASTDATE( 'Primary Date Table'[Date] ) , -3, MONTH ) , [Sales] )

Thanks

[lbendlin]

what's your rationale for doing that? Isn't that skewing the true performance numbers? What if it was a regular day but without sales?

[Anonymous]

Hi 

I'm actually not using it for sales. I used Sales as a simpler way of explain it as most are familiar with it.

I'm calculating to exclude non-productive days. The average number of batches calculated over a 3 month period is being diluted and brought down by non-productive days (i.e the weekend). Sometimes we produce on weekends but if we took 90 days to calculate it for example, and everyday was productive, then it would make sense (the result might be an average of 85% because it's being diluted. But if we're dividing total no. of 'batches' and dividing it by the no. of productive days, say 90-12 (sundays) = 78, then the result would be higher, such as 92% (est) for example.

[lbendlin]

Use an external dates table where you can manually mark the days that had no batches. Then use that flag in your filters.

[Anonymous]

Hi, doesn't work:

Average Right First Time 3 Month Rolling %:=
VAR RFT = 
CALCULATE (
    [Right First Time %],
    FILTER('Primary Date Table','Primary Date Table'[Batches (RFT)]>0)
)
RETURN

AVERAGEX( DATEADD('Primary Date Table'[Date],-3,MONTH)
    		,RFT)

 It's not going back -3 months.

[AntrikshSharma]

Try something like this:

Measure =
AVERAGEX (
    FILTER (
        DATESINPERIOD ( Dates[Date], LASTDATE ( Dates[Date] ), -3, MONTH ),
        [Total Sales] > 0
    ),
    [Total Sales]
)

[Anonymous]

@AntrikshSharma   Hi, thank you for your reply. Sorry, I used sales as a decent example for the same formula but there's been no change when I apply to batches. Basically, when we have zero batches, I don't want to include that day in the 3 month calculation.

Average Right First Time 3 Month Rolling %:=
VAR BATCHES = [No. of Batches (RFT) Fail] + [No. of Batches (RFT) Pass]
RETURN
AVERAGEX(
FILTER(
DATESINPERIOD( 'Primary Date Table'[Date] , LASTDATE( 'Primary Date Table'[Date] ) , -3, MONTH ) , 
BATCHES >0 
),
[Right First Time %]
)