Create a table with a 5 day range without weekend days

[Intellar]

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

[Ahmedx]

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

[vojtechsima · Accepted solution]

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.

[vojtechsima]

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

[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.

[Intellar]

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

[vojtechsima]

Thanks for the feedback, appreciate the kudos.

[sandeephijam]

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
)

[Intellar]

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

[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 💡

[Intellar]

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