cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rnoble
New Member

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?

 

rnoble_0-1604952737045.png

rnoble_1-1604952887437.png

 

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

 

 

1 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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

5 REPLIES 5
Fowmy
Super User
Super User

@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

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

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

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!