Moving Average - Datesinperiod excluding Weekend

[Tuan]

I been trying to caclulate a moving average ignoring weekend dates. I can't seem to figure out how to combine Datesinperiod() with Filter(). How do i filter the date table and then do the moving average?

My moving average calculation

50SMA = 
CALCULATE (
    AVERAGEX ( 'Stock Data', 'Stock Data'[price.close]),
    DATESINPERIOD (
        'Date'[Date],
        LASTDATE ( 'Date'[Date] ),
        -50,
        DAY
    )
)

My Filter

FILTER('Date','Date'[WeekdayNum]<>6 && 'Date'[WeekdayNum] <>0)

[amitchandak · Accepted solution]

Create a date column that does no have a weekend , means blank for week end. Create a rank on it. All wek end will get one other date will get rank. You can use now this in place of Date. -50 will give 50 working days . something like

CALCULATE(Average(Sales[Net Sales]),FILTER(all('Date'),'Date'[WorkDayRank]>=min('Date'[WorkDayRank])-50 && 'Date'[WorkDayRank]<=max('Date'[WorkDayRank])))

The problem with this approach is if someone selects week end in calendar/slicer it will not work.

To make that work, you have to fill the weekend with the last working date.

-1 from Saturday date, -2 from Sunday date

This will keep same rank for week end as friday

WorkingDate = if(WEEKDAY('Date'[Date],2)<6,[Date],[Date]-WEEKDAY('Date'[Date],2)+5)

[Tuan]

Interesting work around. It worked for me.

Thank you very much!