Running Averages that can scale up and down Date Hierarchy

[Swhs24]

Background:

• I have a 7 Day moving average DAX formula
• The date in this formula is joined (by date) to my date table

Problem:
I want to be able to drill-up or down the date hierarchy and have my average represent the level/interval I am viewing within the hierarchy (eg. year, month, week, day). However, every time I change from the date level the formula breaks.

Furthermore, when I attempted to start from scratch building the formula using this formula:

AVERAGEX(
DATESINPERIOD(
‘!Dates (Core Table)’[Date],
VALUES(‘!Dates (Core Table)’[Date]),
-7,
DAY
)

there is no week interval. How can I create a weekly average if that’s not an option?

View of Daily Average:

How can I get weekly or monthly at the same number of intervals (7)?

Please advise.

[Greg]

Hi @Swhs24. Perhaps the “Weekly Comparisons” section of this pattern post can provide a path some additional background:

Non-Standard Time Intelligence

The available DAX date intelligence functions in Power BI handle the comparison with previous periods easily for the standard periods of year, quarter, month, and day when using a standard calendar, or a standard fiscal calendar (where quarters start on a 3-month cadence, e.g., April 1, July 1, etc., and aligned to [but offset from] calendar quarters). These functions do not, however, handle weekly comparisons or custom fiscal calendars. Calculation techniques to handle week comparisons and non-…

Hope it helps.
Greg

[Swhs24]

I’m not clear on the connection here. After reading the linked article you suggested it just reinforced the point that Power BI has a methodology for “standard” time periods, although I’m not certain why a week wouldn’t be considered standard, and that there are alternative methods for what’s non standard and then it runs through examples that are not applicable to my question.

Can you clarify what you would suggest I utilize from your reference please?

Thanks,

[datazoe]

Edit: changed to AVERAGEX.

@Swhs24 I have done something similar, so this may work for you.

First you create your 7 day moving average for each.

7 Avg Daily = VAR LastDate_ =
LASTDATE ( ‘Date’[Date] )
RETURN
AVERAGEX (
CALCULATETABLE (
VALUES ( ‘Date’[Date] ),
FILTER (
ALL ( ‘Date’ ),
‘Date’[Date] <= LastDate_
&& ‘Date’[Date] > DATEADD ( LastDate_, -7, DAY )
)
),
[Sales]
)

7 Avg Weekly = VAR LastDate_ =
LASTDATE ( ‘Date’[Date] )
RETURN
AVERAGEX (
CALCULATETABLE (
VALUES ( ‘Date’[WeekOf] ),
FILTER (
ALL ( ‘Date’ ),
‘Date’[Date] <= LastDate_
&& ‘Date’[Date] > DATEADD ( LastDate_, -49, DAY )
)
),
[Sales]
)

7 Avg Monthly = VAR LastDate_ =
LASTDATE ( ‘Date’[Month] )
RETURN
AVERAGEX (
CALCULATETABLE (
VALUES ( ‘Date’[Month] ),
FILTER (
ALL ( ‘Date’ ),
‘Date’[Month] <= LastDate_
&& ‘Date’[Month] > DATEADD ( LastDate_, -7, MONTH )
)
),
[Sales]
)

7 Avg Yearly = VAR LastDate_ =
MAX ( ‘Date’[Year] )
RETURN
AVERAGEX (
CALCULATETABLE (
VALUES ( ‘Date’[Year] ),
FILTER (
ALL ( ‘Date’ ),
‘Date’[Year] <= LastDate_
&& ‘Date’[Year] > LastDate_ - 7
)
),
[Sales]
)

Then a measure to ensure you are using the right one depending on what you are looking at:

7 Avg Period = SWITCH (
TRUE (),
ISBLANK ( SELECTEDVALUE ( ‘Date’[Date] ) ) = FALSE (), [7 Avg Daily],
ISBLANK ( SELECTEDVALUE ( ‘Date’[WeekOf] ) ) = FALSE (), [7 Avg Weekly],
ISBLANK ( SELECTEDVALUE ( ‘Date’[Month] ) ) = FALSE (), [7 Avg Monthly],
ISBLANK ( SELECTEDVALUE ( ‘Date’[Year] ) ) = FALSE (), [7 Avg Yearly],
BLANK ()
)

Then you can use it like this:

Here is the PBIX for you to reference:
2021-03-27 Rolling Average multiple periods.pbix (178.0 KB)

Respectfully,
DataZoe

[EnterpriseDNA]

Hi @Swhs24, did the response provided by the users and experts help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

[EnterpriseDNA]

Hi @Swhs24, we’ve noticed that no response has been received from you since the 25th of March. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

[EnterpriseDNA]

Hi @Swhs24, due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.

We’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!