cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MP-iCONN
Helper III
Helper III

Get previous date in slicer excluding weekends

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.

 

MPiCONN_0-1664146610657.png

 

Any advice or help on this is greatly appreciated.

 

 

1 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?



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

5 REPLIES 5
edhans
Super User
Super User

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.

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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.

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.

edhans_0-1664204568770.png
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.

edhans_1-1664204902614.png

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

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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.

MPiCONN_1-1664208230475.png

 

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.

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?



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.