How to tweak IsInCurrentWeek to use Monday instead of Sunday as the first day of the week?

[osinquinvdm]

IsInCurrentWeek returns a logical value indicating whether the given Date occurred during the current week, as determined by the current date and time on the system.

Unfortunately the weeks start on Sunday when my weeks actually start on Monday.

What would be your suggestion as for tweaking this in order for IsInCurrentWeek to use Monday instead of Sunday as the first day of the week?

[Phil_Seamark]

You could add something like this to your DATE table

Weeks From Today = 
VAR Offset = WEEKDAY('Dates'[Date],2)
RETURN INT((Today() - ('Dates'[Date] - Offset) ) /7)

The current week will always carry a 0 (And this has been aligned to a Monday start.

This approach has the bonus that you can use the column in your filters and set Report/Page or Visual level filters to only show data for when this is between 0 and 13 if you want a rolling set of weeks.

[osinquinvdm]

I think this is a good track but I want to do it in the back-end, in M not in DAX

[Phil_Seamark]

Good Decision! 🙂

@MarcelBeug , @ImkeF or @Sean will have something for you in no time

[MarcelBeug]

Subtract 1 day from your date: Date.IsInCurrentWeek(YourDate - #duration(1,0,0,0))

[Sean]

Actually here's a link to something related

http://community.powerbi.com/t5/Desktop/Datediff-in-weeks-with-Monday-as-a-start-date/m-p/127846#M54...

[MarcelBeug]

Actually, the first day of the week is culture dependent.

A better solution would be (in this example a column is added indicating if the date is in the current week):

= Table.AddColumn(PreviousStep, "InCurrentWeek", each [YourDate] >= Date.StartOfWeek(DateTime.Date(DateTime.LocalNow()), Day.Monday) and [YourDate] <= Date.EndOfWeek(DateTime.Date(DateTime.LocalNow()), Day.Monday))

[fiveone · Accepted solution]

yep, that's what I ended up with

    #"real Week" = Table.SelectRows(Source, each Duration.Days(Duration.From(Today-[Date]))< Date.DayOfWeek(Today)),

thanks all for putting me on the right track!