Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
OpenDesk_BL
New Member

Date.IsInPreviousWeek Start Week with Monday

Greetings, 

 

I'm trying to build a report where I want to filter from data from last week only however the Date.IsInPreviousWeek function only returns data from the prior Sunday-Saturday. I need my week defined as Monday-Sunday. Is there a way to manipulate this function to give me data for the desired range without having to add additional columns?

1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

Unfortunately, as @ImkeF points out, there isn't a parameter to modify this function to work like you want it to.

 

However, you can write your own equivalent function using functions that do have such a parameter, like Date.StartOfWeek.

 

This should give you the start of the current week:

Date.StartOfWeek(Date.From(DateTime.LocalNow()), Day.Monday)

Then you can write a custom column like

[Date] >= Date.AddWeeks([WeekStart], -1) and [Date] < [WeekStart]

 

AlexisOlson_0-1666549210742.png

 

Here's a full query version that defines WeekStart as a constant rather than materializing it as an custom column:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTQt9Q3MjAyUorVAfOACIVriMo1QuUao3JNULmmqFwzVK45KtcCyo0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Added Weekday" = Table.AddColumn(#"Changed Type", "Weekday", each Date.DayOfWeekName([Date]), type text),
    WeekStart = Date.StartOfWeek(Date.From(DateTime.LocalNow()), Day.Monday),
    #"Added IsInPrevWeek" = Table.AddColumn(#"Added Weekday", "IsInPrevWeek", each [Date] >= Date.AddWeeks(WeekStart, -1) and [Date] < WeekStart, type logical)
in
    #"Added IsInPrevWeek"

 

View solution in original post

2 REPLIES 2
AlexisOlson
Super User
Super User

Unfortunately, as @ImkeF points out, there isn't a parameter to modify this function to work like you want it to.

 

However, you can write your own equivalent function using functions that do have such a parameter, like Date.StartOfWeek.

 

This should give you the start of the current week:

Date.StartOfWeek(Date.From(DateTime.LocalNow()), Day.Monday)

Then you can write a custom column like

[Date] >= Date.AddWeeks([WeekStart], -1) and [Date] < [WeekStart]

 

AlexisOlson_0-1666549210742.png

 

Here's a full query version that defines WeekStart as a constant rather than materializing it as an custom column:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTQt9Q3MjAyUorVAfOACIVriMo1QuUao3JNULmmqFwzVK45KtcCyo0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Added Weekday" = Table.AddColumn(#"Changed Type", "Weekday", each Date.DayOfWeekName([Date]), type text),
    WeekStart = Date.StartOfWeek(Date.From(DateTime.LocalNow()), Day.Monday),
    #"Added IsInPrevWeek" = Table.AddColumn(#"Added Weekday", "IsInPrevWeek", each [Date] >= Date.AddWeeks(WeekStart, -1) and [Date] < WeekStart, type logical)
in
    #"Added IsInPrevWeek"

 

ImkeF
Community Champion
Community Champion

Hi @OpenDesk_BL ,
this behaviour depens on the locale that your file is based on.
So if you could change from US to UK for example, it would take Monday as the first day of the week.
Unfortunately, there is no parameter in the function itself that allows you to limit it just to this formula.

ImkeF_0-1666363493590.png

 



Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.