How to Exclude Blanks in Rolling Average

[v_mark]

I was working on a rolling average for 30 days, everything works fine but I need to exclude those blanks

Rolling Average Changes per Day = 
VAR NumDays = 30
VAR RollingSum = CALCULATE([Ticket Total], 
DATESINPERIOD('Date'[Date], LASTDATE('Date'[Date]), -NumDays, DAY))

RETURN
DIVIDE( RollingSum, NumDays, BLANK())

My concern is it goes through each date on my date table(see right table) 
regardless if there is an input (see left). 
Is there a way to control that behavior and just leave those blanks as blanks as part of the moving average? 

[v-yiruan-msft · Accepted solution]

Hi @v_mark ,

Please try to update your measure [Rolling Average Changes per Day ] as below and check whether it can get your expected result.

Rolling Average Changes per Day =
VAR NumDays = 30
VAR RollingSum =
    CALCULATE (
        [Ticket Total],
        DATESINPERIOD ( 'Date'[Date], LASTDATE ( 'Date'[Date] ), - NumDays, DAY )
    )
RETURN
    IF (
        ISBLANK ( [Ticket Total] ),
        BLANK (),
        DIVIDE ( RollingSum, NumDays, BLANK () )
    )

If the above one can't help you get the correct result, please provide some sample data(exclude sensitive data) and your expected result with calculation logic and special examples. Thank you.

Best Regards

[amitchandak]

@v_mark , Try like

Rolling Average Changes per Day =
VAR NumDays = 30
VAR RollingSum = CALCULATE([Ticket Total],
DATESINPERIOD('Date'[Date], LASTDATE('Date'[Date]), -NumDays, DAY))

RETURN
if(isblank([Ticket Total]), blank(), DIVIDE( RollingSum, NumDays, BLANK()))