Forum Discussion

rnoble's avatar
rnoble
New Member
5 years ago
Solved

Rolling average exclude future dates

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

 

 

  • mahoneypat's avatar
    mahoneypat
    5 years ago

    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

5 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    5 years ago

    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

    • rnoble's avatar
      rnoble
      New Member
      5 years ago

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

    • mahoneypat's avatar
      mahoneypat
      Microsoft Employee
      5 years ago

      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's avatar
        rnoble
        New Member
        5 years ago

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