Filer Day.IsInCurrentWeek to start on Monday

[rhaberkorn4]

Hello,

I am trying to crete a simple spread sheet using and ODBC connection and Power querry. I data dump the ODBC connection into excel and then use that table in Power querry. 

There is a Day of Week going back at least (2) years. I really only care about 'This Current' Week. I filter on 'Day.IsInCurrentWeek' and it starts on a Sunday. I would like it to start Monday to Sunday. 

I have read multiple forms and watched YouTube but my M Coding is not where it needs to be.  Any help would be apprecietated. Thank you

[threw001]

Hi guys

I am trying to do something similar - I am based in Australia/Melbourne, I am using PowerBI Service to refresh my reports. 

I am getting false return for function date.isincurrentweek when the report refreshes on Monday at around 4am Australia/Melbourne time as it is taking the UTC time (which is still in the prior week).

Is there anyway around this? Please see below my query

Thanks

let
    Source = Query1(#date(2016, 1, 1), 5000, #duration(1, 0, 0, 0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "yearWeek", each Date.Year([Date])*100+Date.WeekOfYear([Date])),
    #"Removed Duplicates" = Table.Distinct(#"Added Custom", {"yearWeek"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Duplicates", "Index", 0, 1, Int64.Type),
    #"Renamed Columns1" = Table.RenameColumns(#"Added Index",{{"Index", "WeekCounter"}}),
    #"Added Custom1" = Table.AddColumn(#"Renamed Columns1", "IsCurrentWeek", each Date.IsInCurrentWeek([Date]))
in
    #"Added Custom1"

[Vijay_A_Verma]

If you want to use Monday as the start day for Date.IsInCurrentWeek, use 

Date.IsInCurrentWeek(Date.AddDays([Date],-1))

Hence, you will have to insert following step where #"Changed Type" is previous step

= Table.SelectRows(#"Changed Type", each Date.IsInCurrentWeek(Date.AddDays([Date],-1)))

[rhaberkorn4]

Thank you,

I tried to enter the code you porovided into the advanced editor and it is giving me error.  I have attached the current advianced editor code.

[Vijay_A_Verma]

I can't see Date.IsInCurrentWeek anywhere in your code? If you could highlight, that would be great.

[rhaberkorn4]

Thank you. At the very bottom. Let me know if this is not what you are looking for

[Vijay_A_Verma]

Go to UI - Click on Filtered Rows1 in right pane - In Formula bar, delete everything and copy my code..

[rhaberkorn4]

THANK YOU! I am sorry if this was very frustrating. I really apprecieate your help. This worked. 

Thank you thank you thank you!

[Vijay_A_Verma · Accepted solution]

Use this for your last step

= Table.SelectRows(#"Reordered Columns3", each Date.IsInCurrentWeek(Date.AddDays([Day of Week],-1)))

[rhaberkorn4]

I am so sorry. I am not getting this. Does that code go in place of the #"Filtered Rows1" or after? Does the very last line "in #Filtered Rows1" need to be changed after that. I put the code in and I just get this response.