cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Moving Block Average for Last 5 Business Days (criteria)

Hello,

I'm a beginner when it comes to DAX within Power BI, and I've been trying to solve the following problem:

I need to calculate the average interest rate for the previous 5 business days at the start of each week. My table, which is derived from public data, can be viewed below.

The formula I've come up with is close but fails to filter out business days. I'm really not sure how to do that; I thought that's what the Filter within the Calculate function was for.

Test2DDM5 =
CALCULATE(
AVERAGEX('FRED - Series - DFF', [TotalRate]),
FILTER('FRED - Series - DFF','FRED - Series - DFF'[BusinessDayIndicator] <> 0),
DATESBETWEEN(
'FRED - Series - DFF'[Attribute:date],
'FRED - Series - DFF'[SoWDate]-5,
'FRED - Series - DFF'[EoWDate]-5)
)

In this scenario:
• The Measure [Total Rate] is the sum of all interest rates
• 'FRED - Series - DFF' is the name of the Table
• [SoWDate] is the date of the start of the week
• [EoWDate] is the date of the end of the week
• BusinessDayIndicator indicates whether or not a date/row is a business day or not (1=yes, 0=no)

Any help or guidance is apprecaited. I'm trying to create a calculated column.

1 ACCEPTED SOLUTION

Accepted Solutions
Frequent Visitor

## Re: Moving Block Average for Last 5 Business Days (criteria)

For anyone curious, I ended up with the following code. It requires a Date table.

I don't think this is the most efficient way to do this, but it works for now.

5 Business Day Moving Average =
VAR Last5WorkingDays =
SELECTCOLUMNS(
TOPN(
5,
FILTER(
'Date_Dimension',
'Date_Dimension'[FullDate] <= EARLIER('FRED - Series - DFF'[Attribute:date])-1 && 'GLBL  Date_Dimension'[BusinessDayIndicator] = 1),
Date_Dimension'[FullDate],DESC),
"WorkDay",
'Date_Dimension'[FullDate])
Return
CALCULATE(
SUM(
'FRED - Series - DFF'[Attribute:value]),
'FRED - Series - DFF'[Attribute:date] in Last5WorkingDays)
/5
3 REPLIES 3
Microsoft

## Re: Moving Block Average for Last 5 Business Days (criteria)

Hi, @Flyingstump

Based on my research, you may try to use the following calculated column.

``````Test2DDM5 =
IF (
WeekDay = 1,
CALCULATE (
AVERAGE ( 'FRED - Series - DFF'[Attribute:value] ),
FILTER (
DATESBETWEEN (
ALL('FRED - Series - DFF'[Attribute:date],
EARLIER ( 'FRED - Series - DFF'[Attribute:date] ) - 8,
EARLIER ( 'FRED - Series - DFF'[Attribute:date] ) - 1
),
'FRED - Series - DFF'[BusinessDayIndicator] = 1
)
)
)``````

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Frequent Visitor

## Re: Moving Block Average for Last 5 Business Days (criteria)

Hello @v-alq-msft ,

I tried plugging in your calculated column, but it only resulted in an error. I think you're missing a parenthesis after the ALL function, but even with trying to rectify that, there is an error.

For your convenience, I recreated the file with only the data you need based on the calculation you provided. This file contains public data from the Federal Reserve. However, I don't see a way for me to upload the file for you...

Frequent Visitor

## Re: Moving Block Average for Last 5 Business Days (criteria)

For anyone curious, I ended up with the following code. It requires a Date table.

I don't think this is the most efficient way to do this, but it works for now.

5 Business Day Moving Average =
VAR Last5WorkingDays =
SELECTCOLUMNS(
TOPN(
5,
FILTER(
'Date_Dimension',
'Date_Dimension'[FullDate] <= EARLIER('FRED - Series - DFF'[Attribute:date])-1 && 'GLBL  Date_Dimension'[BusinessDayIndicator] = 1),
Date_Dimension'[FullDate],DESC),
"WorkDay",
'Date_Dimension'[FullDate])
Return
CALCULATE(
SUM(
'FRED - Series - DFF'[Attribute:value]),
'FRED - Series - DFF'[Attribute:date] in Last5WorkingDays)
/5

Announcements

#### New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

#### ‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

#### Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

#### Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

#### Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors