Rolling average exclude future dates

[rnoble]

I'm looking for some assistance in creating a "4-week" rolling average. It's actually going to be a 4-data point rolling average as my snapshots are not always exactly 4-weeks apart. My data has goals going out to the end of the year which seems to drag my rolling average out with it. Below is the data of what I have which creates a plot with a dropping average into the future. The averages seem to be calculating correctly, but I'd like to have them not project out past dates for which I have an amount for. Is there a way to remove the future datapoints from my measure?

4-week average =

var last4weeksofsales = CALCULATETABLE(

TOPN(4,

SUMMARIZE('CW Dataset',

'CW Dataset'[As of date],

"Amount",

sum('CW Dataset'[Amount])

), 'CW Dataset'[As of date], DESC

)

,

FILTER( ALL('Date'[As of date]),

'Date'[As of date] <= SELECTEDVALUE('Date'[As of date])

)

)

return DIVIDE(SUMX(last4weeksofsales, [Amount]), COUNTROWS(last4weeksofsales))

[Fowmy]

@rnoble 

Try your measure with this modification":

4-week average =
VAR last4weeksofsales =
    CALCULATETABLE (
        TOPN (
            4,
            SUMMARIZE (
                'CW Dataset',
                'CW Dataset'[As of date],
                "Amount", SUM ( 'CW Dataset'[Amount] )
            ),
            'CW Dataset'[As of date], DESC
        ),
        FILTER (
            ALL ( 'Date'[As of date] ),
            'Date'[As of date] <= SELECTEDVALUE ( 'Date'[As of date] )
        )
    )
var __Amount = SUMX ( last4weeksofsales, [Amount] )
RETURN
IF(
    ISBLANK(__Amount),
    BLANK(), 
    DIVIDE ( __Amount, COUNTROWS ( last4weeksofsales ) )
)

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[mahoneypat]

Have you tried just changing the ISBLANK in your return to ISBLANK([Amount])?  That way, any date that does not have an Amount value will not show the 4-day average.

Regards,

Pat

[rnoble]

Thanks for the reply @mahoneypat! I get an error when I try that: The value for 'Amount' cannot be determined. Either the column doesn't exist, or there is no current row for this column.

4-week average =

VAR last4weeksofsales =

CALCULATETABLE (

TOPN (

4,

SUMMARIZE (

'CW Dataset',

'CW Dataset'[As of date],

"Amount", SUM ( 'CW Dataset'[Amount] )

),

'CW Dataset'[As of date], DESC

),

FILTER (

ALL ( 'Date'[As of date] ),

'Date'[As of date] <= SELECTEDVALUE ( 'Date'[As of date] )

)

)

var __Amount = SUMX ( last4weeksofsales, [Amount] )

RETURN

IF(

ISBLANK([Amount]),

BLANK(),

DIVIDE ( __Amount, COUNTROWS ( last4weeksofsales ) )

)

[mahoneypat · Accepted solution]

Sorry.  I thought [Amount] was a measure in your original shown table.  You can try to replace that with ISBLANK(SUM('CW Dataset'[Amount])).

Regards,

Pat

[rnoble]

@Fowmy Thanks for taking a look. I tried what you suggested and nothing changed. Other thoughts? Am I asking the wrong question?