Moving Block Average for Last 5 Business Days (criteria)

[Anonymous]

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.

[Anonymous · Accepted solution]

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

[v-alq-msft]

Hi, @Anonymous 

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.

[Anonymous]

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