Moving Weekly Average Excluding Weekends

[mbailey]

I know there's been many posts on moving averages and I have tried many suggestions but can't seem to find a simple one that will work.

I have two tables I'm working with. The first, DMTA_OnTimeShippingSummary has columns: due_date, number_of_order_lines_to_ship, number_of_order_lines_shipped, percent_in_time_shippng. 

The second table is a Date_Table. There is a relationship between the two tables on DMTA_OnTimeShippingSummary[due_date] and Date_Table[Date]. The Date_Table has a column, IsWorkday, with values of 1 for a workday and 0 for weekends and holidays. Table examples below.

I want to create a Measure that calculates a moving weekly average excluding weekends and holidays.

Based on another post (Link ) I've attempted this formula below, but It gives an error (highlighted) of "EARLIER/EARLIEST refers to an earlier row context which does not exist."

One thing I noticed, if it matters, as shown in the screenshots below is that the Date_Table[Date] field has a calendar icon next to it, but the  DMTA_OnTimeShippingSummary[due_date] does not - even though both are  DataType: Date/Time.

If this can be fixed - great, if someone has a better solution - even better.

Thank you in advance for any help and suggestions!  -Mike

Weekly Avg =

VAR Last5Workdays =

SELECTCOLUMNS (

TOPN (

5,

FILTER (

Date_Table,

Date_Table[Date] <= EARLIER (DMTA_OnTimeShipmentSummary[due_date])  <---- locaton of error

&& Date_Table[IsWorkday] = 1

),

Date_Table[Date], DESC

),

"Workday", Date_Table[Date]

)

RETURN

CALCULATE (

SUM ( DMTA_OnTimeShipmentSummary[percent_on_time_shipment] ),

DMTA_OnTimeShipmentSummary[due_date] IN Last5Workdays) / 5

Tables:

[Anonymous]

Hi @mbailey ,

Is your formula a measure?

EARLIER() is a DAX function that acts exclusively on row context, and its purpose is to create a reference to a column value on an outer loop of the evaluation of the expression. It is commonly used in calculated columns during nested row by row iterations.

Best Regards,

Jay

[mbailey]

To answer your question, Yes, this is a Measure I'm trying to create.  But, I'm not sure if this is the best or correct way to accomplish what I'm wanting. I'm assuming your response means that I can't use the function in this manner. So, I guess then the question becomes: How can I calculate the moving weekly average given my data?

Thanks for your help,

Mike 

[MFelix]

Hi @mbailey ,

Have you tried replacing the filter function with the information I provided?

Use the Date table column on the earlier function?

[mbailey]

Yes, and it still produces an error. See below.

[MFelix]

Hi @mbailey ,

Not really sure if I got this correctly but believe that you don't need to have the isworking day.

If you pickup the maximum date - 6 days you will get all values within the week so last 5 days.

Try the following measure:

Weekly Avg =
CALCULATE (
    SUM ( DMTA_OnTimeShipmentSummary[percent_on_time_shipment] );
    FILTER (
        ALL ( DMTA_OnTimeShipmentSummary[due_date] );
        DMTA_OnTimeShipmentSummary[due_date] <= MAX ( Date_Table[Date] )
            && DMTA_OnTimeShipmentSummary[due_date]
                >= SELECTEDVALUE ( Date_Table[Date] ) - 6
    )
) / 5

Check if the calculations is correct based on your values. If not the only adjustment that may need to be done is the calculation to pick up the lower date.

[MFelix]

Hi @mbailey ,

The error on your measure is that you need to refer to the Date column so in your case the formula for the filter would be:

FILTER (
Date_Table,
Date_Table[Date] <= EARLIER (Date_Table[Date])  <---- locaton of error
&& Date_Table[IsWorkday] = 1
)

Using the EARLIER function you are abasically picking up the previous value of the date column so since you are in a calculated column that uses the row context you cannot pickup values from other tables.

[mbailey]

Hi Miguel,

I'm assuming that based on the reply after yours that I can't use this formula in the manner I was trying. So I guess I'm back to just asking how can I create the moving weekly average?

Thanks for your help,

Mike