cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Flyingstump
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.

 

SampleData - Interest Rates.PNG

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Flyingstump
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

View solution in original post

3 REPLIES 3
Microsoft v-alq-msft
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
        )
    )
)

 

 

 

If it doesn't work. Please show me your sample pbix file. Do mask sensitive data before uploading. Thanks.

 

Best Regards

Allan

 

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

 

Flyingstump
Frequent Visitor

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

Hello @v-alq-msft ,

Thank you for your reply.

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

 

 

Flyingstump
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

View solution in original post

Helpful resources

Announcements
New Ranks Launched March 24th!

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!

‘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

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 Microsoft Power Platform event series.

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

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