Forum Discussion

rhaberkorn4's avatar
rhaberkorn4
New Member
2 years ago
Solved

Filer Day.IsInCurrentWeek to start on Monday

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

Need Help
Tutorial Requests
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    2 years ago

    Use this for your last step

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

9 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    2 years ago

    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's avatar
      rhaberkorn4
      New Member
      2 years ago

      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's avatar
        Vijay_A_Verma
        Most Valuable Professional
        2 years ago

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

  • threw001's avatar
    threw001
    Helper III
    2 years ago

    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"