cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Create a table with a 5 day range without weekend days

Hi,

So i am trying to calculate the following but im stuck.
I want to create a table that always shows 5 dates.
The dates should change depending on the current day and it should always be the previous 2 working days, the current day, and the next 2 working days.

Everytime i try to create something it either is missing dates or shows a friday next week instead of last.
sometimes i get it right for 1 day but the next it is messed up or it suddenly shows 8 days instead of 5.

For example:

if Today = Monday:
- Last Thursday
- Last Friday
- Monday(Today)
- Next Tuesday
- Next Wednesday

if Today = Tuesday
- Last Friday
- Last Monday
- Tuesday(Today)
- Next Wednesday
- Next Thursday

if Today = Wednesday
- Last Monday
- Last Tuesday
- Wednesday (Today)
- Next Thursday
- Next Friday

Thank you for the help in advance 🙂

``````5 days =
VAR CurrentDate = TODAY()
VAR CustomCalendar =
FILTER (
CALENDAR ( CurrentDate - 2, CurrentDate + 2 ),
WEEKDAY ( [Date], 2 ) >= 1 && WEEKDAY ( [Date], 2 ) <= 5
),
"DayOfWeek", WEEKDAY ( [Date], 2),
"FormattedDate", FORMAT([Date], "ddd dd-MM")
)
RETURN
CustomCalendar``````

This gives the following result

While my expected result is
12-10-2023
13-10-2023
16-10-2023
17-10-2023
18-10-2023

And this should change per day

1 ACCEPTED SOLUTION
Memorable Member

Hello, @Intellar,

do it in power query like this.

New query -> Blank Query -> go to Advanced Editor and paste this:

``````let
today = Date.From(DateTime.FixedLocalNow()),
start = today - #duration(8,0,0,0),
end = today + #duration(8,0,0,0),
listDates = List.Dates(start, Number.From(end-start)+1, #duration(1,0,0,0) ),
removeWeekends = List.Select(listDates, each  not List.Contains({"Sunday", "Saturday"}, Date.DayOfWeekName( Date.From(_), "en"))),
positionOfToday = if List.PositionOf(removeWeekends, today) = -1 then  List.PositionOf(removeWeekends, List.Max(List.Select(removeWeekends, each Date.From(_) < today ))) else  List.PositionOf(removeWeekends, today),
correctPositions = {positionOfToday-2..positionOfToday+2},
finalList = List.Transform(correctPositions, each removeWeekends{_} ),
#"Converted to Table" = Table.FromList(finalList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Date"}})

in
#"Renamed Columns"``````

EDIT:
If today is weekend, it's gonna take Friday as today.

11 REPLIES 11
Super User

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

https://1drv.ms/u/s!AiUZ0Ws7G26Rix23GbAmin-06rQ8?e=Slwe6W

Memorable Member

Hello, @Intellar,

do it in power query like this.

New query -> Blank Query -> go to Advanced Editor and paste this:

``````let
today = Date.From(DateTime.FixedLocalNow()),
start = today - #duration(8,0,0,0),
end = today + #duration(8,0,0,0),
listDates = List.Dates(start, Number.From(end-start)+1, #duration(1,0,0,0) ),
removeWeekends = List.Select(listDates, each  not List.Contains({"Sunday", "Saturday"}, Date.DayOfWeekName( Date.From(_), "en"))),
positionOfToday = if List.PositionOf(removeWeekends, today) = -1 then  List.PositionOf(removeWeekends, List.Max(List.Select(removeWeekends, each Date.From(_) < today ))) else  List.PositionOf(removeWeekends, today),
correctPositions = {positionOfToday-2..positionOfToday+2},
finalList = List.Transform(correctPositions, each removeWeekends{_} ),
#"Converted to Table" = Table.FromList(finalList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Date"}})

in
#"Renamed Columns"``````

EDIT:
If today is weekend, it's gonna take Friday as today.

Memorable Member

I edited the query, there was bug in case of weekends, thanks @Ahmedx  for pointing out.

Super User

look what happens if today is today = #date(2023, 10, 22) or today = #date(2023, 10, 21)

Memorable Member

@Ahmedx Very good point, I didn't check for weekened, thanks, I edited the solution.

Frequent Visitor

@vojtechsima this seems to be the solution thanks for the help!

Memorable Member

Thanks for the feedback, appreciate the kudos.

Helper I

see if this  measure work

LastMonday =
VAR Today = TODAY()
VAR DayOfWeek = WEEKDAY(Today)
RETURN
SWITCH(
DayOfWeek,
1, Today - 6,
2, Today - 7,
3, Today - 8,
4, Today - 9,
5, Today - 10,
6, Today - 11,
7, Today - 12
)

LastFriday =
VAR Today = TODAY()
VAR DayOfWeek = WEEKDAY(Today)
RETURN
SWITCH(
DayOfWeek,
1, Today - 2,
2, Today - 3,
3, Today - 4,
4, Today - 5,
5, Today - 6,
6, Today - 7,
7, Today - 8
)

NextTuesday =
VAR Today = TODAY()
VAR DayOfWeek = WEEKDAY(Today)
RETURN
SWITCH(
DayOfWeek,
1, Today + 2,
2, Today + 3,
3, Today + 4,
4, Today + 5,
5, Today + 6,
6, Today + 7,
7, Today + 8
)

NextFriday =
VAR Today = TODAY()
VAR DayOfWeek = WEEKDAY(Today)
RETURN
SWITCH(
DayOfWeek,
1, Today + 5,
2, Today + 6,
3, Today + 7,
4, Today + 8,
5, Today + 9,
6, Today + 10,
7, Today + 11
)

Frequent Visitor

@sandeephijam
Thank you for your input but my post was incomplete and that might made it harder to understand.
I added my current code and results could you please check again if you have a moment?

Super User

Hello @Intellar ,

refer to this video https://youtu.be/2Ea85_BNGp4?si=i8sAXp88f3shUR7X

 Vote for my Community Mobile App Idea

Proud to be a Super User!

Frequent Visitor

it was informative unfortunatly not what i was looking for but thank you for the input!