Forum Discussion
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 🙂
Edit1 : Code added
5 days =
VAR CurrentDate = TODAY()
VAR CustomCalendar =
ADDCOLUMNS (
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
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
- vojtechsima
Super User
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.- Ahmedx
look what happens if today is today = #date(2023, 10, 22) or today = #date(2023, 10, 21)
- vojtechsima
Ahmedx Very good point, I didn't check for weekened, thanks, I edited the solution.
vojtechsima this seems to be the solution thanks for the help!
- vojtechsima
Thanks for the feedback, appreciate the kudos.
- vojtechsima
I edited the query, there was bug in case of weekends, thanks Ahmedx for pointing out.
- Ahmedx
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
- Idrissshatila
Hello Intellar ,
refer to this video https://youtu.be/2Ea85_BNGp4?si=i8sAXp88f3shUR7X
If I answered your question, please mark my post as solution, Appreciate your Kudos 👍
Follow me on Linkedin
Vote for my Community Mobile App Idea 💡Thank you for the link
it was informative unfortunatly not what i was looking for but thank you for the input!
- sandeephijam
Helper II
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
)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?
