Get previous date in slicer excluding weekends

[MP-iCONN]

Is there any easy way to get the previous days results with a slicer, excluding weekends?

My data consists of a start time and and end time in two columns and I am just trying to get the results of all the returned records for each previous days start time date, excluding any Saturdays and Sundays.  I know I am showing a date/time column but I will be having a Date Only column that will be the filtered on date.

Any advice or help on this is greatly appreciated.

[edhans]

Best way is to add a column in Power Query to your date table.

Date.DayOfWeek([Date], Day.Saturday))

Then filter your slicer when that value is >= 2. 

0 and 1 will be Saturday and Sunday.

[MP-iCONN]

Thank you.  That works to help exclude weekends but not sure how to always get the previous day.  I wish we could filter out Saturdays and Sundays with the relative date slicer.  I think I need a custom dynamic date slicer that excludes the weekends, just not sure how to do that.

[edhans]

You add the Week Day number to the slicer, then filter out 0 and 1.

Below the Date field is in the slicer, but I have added the weekday number to the filter on the visual and told it to only keep weekdays >= 2, so the date slicer is missing all weekends. You could expand that logic to also exclude holidays if you have a holiday schedule you could add in there.

I am also not sure what this comment means: "That works to help exclude weekends but not sure how to always get the previous day." Not sure what weekends have to do with previous day.

If you want to find the previous day in a report and exclude weekends, so for Monday, previous day is Friday, then same basic logic. It finds the previous day excluding weekends, so if you look at Sept 26 (monday) it finds the previous day is the 23rd, Friday. For Sat and Sun, it also shows the "previous day" is still Friday. It will never return Sat/Sun as the answer.

Previous Day = 
VAR varCurrentDay = SELECTEDVALUE('Date'[Date])
VAR Result =
    CALCULATE(
        MAX('Date'[Date]),
        REMOVEFILTERS('Date'),
        'Date'[Date] < varCurrentDay && 'Date'[Weekday Number] >= 2
    )
RETURN
    Result

[MP-iCONN]

Thanks for the help on this.  I might not be explaining myself as good as I should.

I didn't know if you could get a custom date slicer (top left) to always select the previous day, so if it is saturday it will be selected on the Friday's date.

I have to always show the previous days results for a punch compliance report (punch in and out).  So every single record has to be for the previous day.  

Saturday = 09/24/2022 but the Slicer will show the previous work day, Friday 09/23/2022. 

Sunday = 09/25/2022 but the Slicer will show the previous work day, Friday 09/23/2022.

Monday = 09/26/2022 but the Slicer will show the previous work day, Friday 09/23/2022.

Tuesday = 09/27/2022 then shows the Previous Day, Monday 09/26/2022

and so on...

Thank you.

[edhans · Accepted solution]

No. You cannot preselect a date in a slicer unfortunately. We would need the PowerFX language on top of the report to do that. Users will need to select the date they want to see. This is a longstanding request to have slicers automatically show a specific value based on today's date or other criteria.

If there are any other questions, please let me know. Otherwise can you please mark 1 or more posts in this thead as a solution to at least the original question so this thread can be marked as solved?