cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
HiltonM Frequent Visitor
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];
                DATEADD(__LAST_DATE;-25; DAY);
            __LAST_DATE
            );
            CALCULATE([Market Share])
        )
 
 
 
kind regards,
HiltonM
1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-yuezhe-msft
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 v-yuezhe-msft
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.
HiltonM Frequent Visitor
Frequent Visitor

Re: 25 days rolling average including weekends

hi @v-yuezhe-msft 

 

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?