Rolling Averages - 3 Days

[Anonymous]

Hello Everyone,

I am looking to calculate a rolling average of production units over a given time period, say 3 days.  I have a calendar table and a table of production values by day.  These tables are set-up via relationships and the relationship is working based on what I see in other visualizations.

I have done some basic DAX and am able to get the three day average except when I span a non-working day (NWD).  However, I am not able to get it to work for days that span NWDs.  For example, using a 3 day rolling average, every Monday shows the rolling average is whatever was produced on Monday becuase it is also counting Saturday and Sunday, which are 0 value days typically.

Further complicating issues is that we do occassionally work weekends, so I do not want to just factor in something based on day of the week, similar to NETWORKDAYS in Excel.

My current method of calculating the rolling average is I take a sum of produced units over number of days (i.e. three days) and divide that by the distinct count of days (i.e. 3 days).  I've tried using filters to look only at days where production is >0, but I cannot seem to get it to work.

My base DAX is below.

'Total Band - Rolling Average 1 =

'var total_bands_in_period = calculate(sum('Production Report - PIC'[Total 'Bands]),DATESINPERIOD(CalendarDate[Date],LASTDATE(CalendarDate[Date]),-3,DAY))

'var days_in_period = 'CALCULATE(DISTINCTCOUNT(CalendarDate[Date]),DATESINPERIOD(CalendarDate[Date],LASTDATE(CalendarDate[Date]),-3,DAY))

'return

'total_bands_in_period/days_in_period

Any guidance would be appreciated.

Thank you,

Rich

[PaulDBrown · Accepted solution]

@Anonymous 

try this (you'll need to adjust the code to your tables/measures):

Average sales last 3 working days =
VAR currentdate =
    MAX ( 'CalendarDate'[Date] )
VAR _Table =
    TOPN (
        3,
        FILTER (
            ALL ( 'CalendarDate' ),
            'CalendarDate'[Date] <= currentdate
                && NOT ( ISBLANK ( [your sum measure] ) )
        ),
        'CalendarDate'[Date], DESC
    )
RETURN
    IF (
        ISBLANK ( [your sum measure] ),
        BLANK (),
        AVERAGEX( _Table, [your sum measure] )
    )

PS, You could even make the number of rolling days dynamic by creating a slicer with a range of numbers and using SELECTEDVALUE(Slicer[number]) as the number of days you wish in the TOPN (instead of the "3").

[Anonymous]

Thank you PaulDBrown.  This worked for me.  Appreciate the help.

[PaulDBrown]

@Anonymous 

Just in case my previous suggestion is a hindrance on performance, here is an alternative:

Alternative =
VAR Date1 =
    MAX ( 'CalendarDate'[Date] )
VAR Date2 =
    MAXX (
        FILTER (
            ALL ( 'CalendarDate'[Date] ),
            'CalendarDate'[Date] < Date1
                && NOT ( ISBLANK ( [Your sum measure] ) )
        ),
        'CalendarDate'[Date]
    )
VAR Date3 =
    MAXX (
        FILTER (
            ALL ( 'CalendarDate'[Date] ),
            'CalendarDate'[Date] < Date2
                && NOT ( ISBLANK ( [Your sum measure] ) )
        ),
        'CalendarDate'[Date]
    )
VAR Sum1 =
    CALCULATE ( [Your sum measure], 'CalendarDate'[Date] = Date1 )
VAR Sum2 =
    CALCULATE ( [Your sum measure], 'CalendarDate'[Date] = Date2 )
VAR Sum3 =
    CALCULATE ( [Your sum measure], 'CalendarDate'[Date] = Date3 )
RETURN
    IF (
        ISBLANK ( [Your sum  measure] ),
        BLANK (),
        DIVIDE ( Sum1 + Sum2 + Sum3, 3 )
    )

[parry2k]

@Anonymous or this longer route but it breaks down, product sum of last 3 days, number of days and then divide to calculate the average. 

Avg Last 3 Prod Days = 
VAR __table = 
CALCULATETABLE ( 
    CALCULATETABLE ( VALUES ( 'Calendar'[Date] ), Prod ), 
    DATESINPERIOD ( 'Calendar'[Date], LASTDATE ( 'Calendar'[Date] ), -3, DAY )
)
VAR __production = CALCULATE ( [Sum Prod], __table )
VAR __days = COUNTROWS ( __table )
RETURN DIVIDE ( __production, __days )

[parry2k]

@Anonymous there are few ways to solve this, here is one way of doing it, Sum Prod is simply SUM measure for production qty 

Avg Last 3 days for Production Days Only = 
AVERAGEX (
    DATESINPERIOD (
        'Calendar'[Date],
        LASTDATE ('Calendar'[Date] ),
        -3,
        DAY
    ), 
    [Sum Prod]
)

[MattAllington]

interesting little problem. I have a few ideas but need to test it first.