cancel
Showing results for
Did you mean:
Frequent Visitor

## 25 days rolling average including weekends

good day,

I am working on a report where I have to calculate the rolling 25 days average using the market share. I used the quick measures to calculate my 25 days average and when I do a manual test in calculating, I found out that my formula does not take the past 25 working days, it counts 25 days including weekends. Can someone help with how to exclude weekends?

the formula used is

25 day MA =

VAR __LAST_DATE = LASTDATE('Production Dim_Date'[Date].[Date])
RETURN
AVERAGEX(
DATESBETWEEN(
'Production Dim_Date'[Date].[Date];
__LAST_DATE
);
CALCULATE([Market Share])
)

kind regards,
HiltonM
1 ACCEPTED SOLUTION

Accepted Solutions
Moderator

## Re: 25 days rolling average including weekends

@HiltonM ,

Firstly,  create a calendar table using calendar() function, create relationship between the calendar table and your orginal table using date field.

Secondly,create the following columns in your calendar table.

`WeekDay = WEEKDAY('Calendar'[Date])`
`If work day = IF(OR('Calendar'[WeekDay]=1,'Calendar'[WeekDay]=7),0,1)`

At last, create the following measure in your original table.

```25 day MA =
VAR WorkingDays = FILTER(
CALCULATETABLE( Calendar,
DATESINPERIOD( 'Calendar'[Date], LASTDATE( 'Calendar'[Date] ), -25, DAY) ),
'Calendar'[If work day]= 1 )

RETURN
AVERAGEX(
WorkingDays,
[Market Share] + 0 )```

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Moderator

## Re: 25 days rolling average including weekends

@HiltonM ,

Firstly,  create a calendar table using calendar() function, create relationship between the calendar table and your orginal table using date field.

Secondly,create the following columns in your calendar table.

`WeekDay = WEEKDAY('Calendar'[Date])`
`If work day = IF(OR('Calendar'[WeekDay]=1,'Calendar'[WeekDay]=7),0,1)`

At last, create the following measure in your original table.

```25 day MA =
VAR WorkingDays = FILTER(
CALCULATETABLE( Calendar,
DATESINPERIOD( 'Calendar'[Date], LASTDATE( 'Calendar'[Date] ), -25, DAY) ),
'Calendar'[If work day]= 1 )

RETURN
AVERAGEX(
WorkingDays,
[Market Share] + 0 )```

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

## Re: 25 days rolling average including weekends

I have implemented the solution in my case and still gives out an incorrect answer. Can I send you a link to my pbix file maybe you can see what I am talking about?