This week filter

[MauriceWhelan]

Hi.

I have created a slice and I want my date filter to be MON - FRI of the current week. I have tried something like [Date]>=TODAY() - WEEKDAY(TODAY()) + 1. Does not seem to be working. Am I on the right track here?

Any help would be great, thank you.

[1minManager]

Try:

AND(
WEEKNUM([Date])=WEEKNUM(Today()),
OR(
WEEKDAY(TODAY())=2,
WEEKDAY(TODAY())=3,
WEEKDAY(TODAY())=4,
WEEKDAY(TODAY())=5,
WEEKDAY(TODAY())=6
))

There are more elegant ways, but this is the easiest to understand

Simon@1minManager.com

[MauriceWhelan]

Many thanks for this @1minManager.

[Aleksi]

You would need to add the YEAR() as well. Otherwise it will give wrong result next year.

[MauriceWhelan]

@Aleksi, thank you for that.

Can I just ask how to incorporate that into the expression?

[Aleksi]

AND(
WEEKNUM([Date])=WEEKNUM(Today()),
YEAR([Date])=YEAR(TODAY()),
OR(
WEEKDAY(TODAY())=2,
WEEKDAY(TODAY())=3,
WEEKDAY(TODAY())=4,
WEEKDAY(TODAY())=5,
WEEKDAY(TODAY())=6
))

[MauriceWhelan]

Thanks a million @Aleksi

[Steve · Accepted solution]

Try:

AND(
  ([Date] >= (TODAY() - WEEKDAY(TODAY()) + 2)),
  ([Date] <= (TODAY() - WEEKDAY(TODAY()) + 6))
)

[Aleksi]

Yes, you are correct… it doesn’t handle Dec/Jan transition. I didn’t think deeply enough

[MauriceWhelan]

Worked perfectly, thank you @Steve

[NoobStarter]

AND(
([Date] >= (TODAY() - WEEKDAY(TODAY()) + 2)),
([Date] <= (TODAY() - WEEKDAY(TODAY()) + 6))
)

So this works with Dec/Jan transition?? Dont wan to add it before i know )

[EmadK]

Hi Steve, if the working days are from Sunday to Thursday (MENA region), how will this expression change? 

[Suvrutt_Gurjar]

You may try  this expression for Sunday to Thursday time frame.

AND([date]>=EOWEEK(TODAY())-6 , [date]<=EOWEEK(TODAY())-2)

[EmadK]

Thanks for the answer.

SELECT(Customers[Order No],
AND(
[_THISROW].[Customer ID] = [Customer ID],
DATE([Date & Time]) >= EOWEEK(TODAY())-6,
[Status] = "Paid"
)
)

Here's the expression I am trying to work with. 
Basically, I want to filter all Order No (orders) created starting from Sunday till Thursday. I am not sure what I am doing wrong here 😅

[Suvrutt_Gurjar]

Please try and please test well for many dates

SELECT(Customers[Order No],
AND(
[_THISROW].[Customer ID] = [Customer ID],
DATE([Date & Time]) >= EOWEEK(TODAY())-6,
DATE([Date & Time])<=EOWEEK(TODAY())-2,
[Status] = "Paid"
)
)

[EmadK]

This worked! Thank you so much 🙂