topic Re: 3 Month Rolling Averages - How to exclude the days with no sales in DAX Commands and Tips

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

Anonymous — Fri, 31 Jul 2020 15:09:49 GMT

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

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

AntrikshSharma — Fri, 31 Jul 2020 16:22:20 GMT

Try something like this:

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

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

lbendlin — Sat, 01 Aug 2020 20:25:35 GMT

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?

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

Anonymous — Mon, 03 Aug 2020 08:13:46 GMT

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.

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

Anonymous — Mon, 03 Aug 2020 10:39:46 GMT

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 %] )

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

lbendlin — Mon, 03 Aug 2020 12:26:09 GMT

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

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

Anonymous — Mon, 03 Aug 2020 16:01:53 GMT

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.