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 goal...
  • 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

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 ) )
      )
      • mahoneypat's avatar
        mahoneypat
        Microsoft Employee
        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