cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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?

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
Employee

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!

5 REPLIES 5
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 🙂

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

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!

New Member

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

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!

New Member

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors