I have a PowerBI report where I would like to skip the weekend days in a table I am displaying. For example, I need to show only working days. I have a table shown below that shows days of the week, Instead of showing 02.20.2021 which is a Saturday I want to display the following Monday 02.22.2021. So the table would display Friday then the next column would be Monday and so on…See screen shot below and I have also attached the report as well.
AssemblySchedule - Weekend not working Rework.pbix (319.8 KB)
Just to clarify, what you want is to essentially only show weekdays, right?
A number of different ways you can do this, but the simplest is probably just unchecking weekend in the filter pane:
- Brian
1 Like
Beat me to it! I was going to suggest that and add the dax to skip weekends in a few minutes.
FYI - If you’re interested in the DAX and/or Power Query approaches to removing weekend days, I did a video on that last year:
- Brian
Hi @Preston
Is this you looking for?
AssemblySchedule - Weekend not working Rework - jbressan.pbix (319.1 KB)
I did two changes
4DaysAhead =
IF (
WEEKDAY ( TODAY () + 4 ) = 1,
--"Weekend",
IF (
TODAY () + 5 >= Workticketstepheadermerge[Step Start Date]
&& TODAY () + 5 <= Workticketstepheadermerge[Step Due Date],
Workticketstepheadermerge[Employees],
BLANK()
),
IF (
WEEKDAY ( TODAY () + 4 ) = 7,
--"Weekend",
IF (
TODAY () + 6 >= Workticketstepheadermerge[Step Start Date]
&& TODAY () + 6 <= Workticketstepheadermerge[Step Due Date],
Workticketstepheadermerge[Employees],
BLANK()
)
,
IF (
TODAY () + 4 >= Workticketstepheadermerge[Step Start Date]
&& TODAY () + 4 <= Workticketstepheadermerge[Step Due Date],
Workticketstepheadermerge[Employees],
BLANK()
)
)
)
and
4DaysAhead =
IF (
WEEKDAY ( TODAY () + 4 ) = 1,
TODAY () + 5,
IF (
WEEKDAY ( TODAY () + 4 ) = 7,
TODAY () + 6 ,
TODAY () + 4
)
)Another option would be to add a column to the Dates table to determine if it is a workday or not then update the measure 4 days ahead with:
Measure in Dates Table
4DaysAhead = CALCULATE (
MIN ( Dates[Date] ),
FILTER (
ALL ( Dates[Date], Dates[IsWorkingDay] ),
Dates[Date] > TODAY () + 4
&& Dates[IsWorkingDay] = TRUE
)
)
Column in Dates Table:
IsWorkingDay = NOT WEEKDAY( ‘Dates’[Date] ) IN { 1,7 }
Updated Custom column in Workticketstepheadermerge
4DaysAhead = If([4DaysAhead] >= Workticketstepheadermerge[Step Start Date] &&[4DaysAhead] <= Workticketstepheadermerge[Step Due Date],Workticketstepheadermerge[Employees], “”)
AssemblySchedule - Weekend not working Rework.pbix (326.8 KB)
Thank you for the quick response. I tried this and its very close…however I am having a hard time getting the logic working for when its more than 4 days ahead. I need to go all of the way up to 11 days ahead. I am struggling with the pattern I need to follow for the below measure. Can you help?
I just incremented all of your numbers below by one, however that doesn’t work for 5DaysAhead as its displaying 02.22.2021. Can you help me with the pattern I need to follow?
4DaysAhead =
IF (
WEEKDAY ( TODAY () + 4 ) = 1,
TODAY () + 5,
IF (
WEEKDAY ( TODAY () + 4 ) = 7,
TODAY () + 6 ,
TODAY () + 4
)
)
Hi @Preston
Try to follow this Pattern
1DayAhead =
VAR _Day = TODAY () + 1
Return
IF (
WEEKDAY ( _Day ) = 1,
_Day + 1,
IF (
WEEKDAY ( _Day ) = 7,
_Day + 2 ,
_Day
)
)
For the rest of NDaysAhead
VAR _Day = (N-1)DaysAhead +1
Sample 10DayAhead
VAR _Day = [9DayAhead) + 1
2DaysAhead =
VAR _Day = [1DayAhead] + 1
Return
IF (
WEEKDAY ( _Day ) = 1,
_Day + 1,
IF (
WEEKDAY ( _Day ) = 7,
_Day + 2 ,
_Day
)
)
With this Pattern update the calculated column
4DaysAhead =
IF (
[4DaysAhead] >= Workticketstepheadermerge[Step Start Date] &&
[4DaysAhead] <= Workticketstepheadermerge[Step Due Date],
Workticketstepheadermerge[Employees],
BLANK()
)
Days Current to 4
Days 5 to 11
AssemblySchedule - Weekend not working Rework - jbressan.pbix (319.7 KB)
1 Like
This worked perfect! Thanks alot!
1 Like
I appreciate your help as well!
1 Like
- Really nice job on this solution.
1 Like
Glad to help and great job @jbressan!
1 Like