Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

If Date is a Monday then return previous Friday and Exclude Holidays

Hello, I am looking for some help on a calculation I am doing. I just need help calculating the previous day excluding weekends and holidays. I have a date table setup with the holidays and weekends already mapped and I was using a calculation to get the previous day excluding weekends, but I do not know how to also configure it to skip holidays. Any help would be appreciated. Here is the calc I am currently using from another post.

 

 

_PreviousDay = 
VAR _Date =
    dimDate[Date]
VAR _latestDate = _Date - 1
VAR _DD =
    SWITCH (
        WEEKDAY ( _Date ),
        2, _Date - 3,
        1, _Date - 2,
        7, _Date - 1,
        _latestDate
    )
RETURN
   _DD

 

 

Need Help
  • lbendlin's avatar
    lbendlin
    2 years ago

    You posted this in the Power Query board. Do you want the result in Power Query or in DAX?

     

    _PreviousDay = 
VAR _Date =
    [Date]
RETURN MAXX(FILTER(dimDate,[Date]<_Date && [Holiday Or Weekend]=FALSE()),[Date])

1 Reply

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

    You posted this in the Power Query board. Do you want the result in Power Query or in DAX?

     

    _PreviousDay = 
VAR _Date =
    [Date]
RETURN MAXX(FILTER(dimDate,[Date]<_Date && [Holiday Or Weekend]=FALSE()),[Date])