25 days rolling average including weekends

[Anonymous]

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

[v-yuezhe-msft · Accepted solution]

@Anonymous ,

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

[Anonymous]

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?