Forum Discussion

Tuan's avatar
Tuan
Icon for Helper III rankHelper III
6 years ago
Solved

Moving Average - Datesinperiod excluding Weekend

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)

 

Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    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)

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    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's avatar
      Tuan
      Icon for Helper III rankHelper III
      6 years ago

      Interesting work around. It worked for me.

       

      Thank you very much!