Forum Discussion

mb0307's avatar
mb0307
Responsive Resident
4 years ago
Solved

Week offset in Power Query

Hi all,

 

I have this measure in DAX which works absolutely fine.   I want this to be in Power Query, please help:

Week Offset = 
Var StartOfWeek = 'Calendar'[Date] - WEEKDAY( 'Calendar'[Date], 2)+1
Var StartOfCurrentWeek = TODAY() - WEEKDAY(TODAY(), 2)+1
Return (StartOfWeek - StartOfCurrentWeek)/7

 

Thanks much 

Need Help
  • VahidDM's avatar
    VahidDM
    4 years ago

    Hi mb0307 

     

    Add a Custom column with this code in Power Query:

    (Date.StartOfWeek([Date])-Date.StartOfWeek(DateTime.Date(DateTime.LocalNow())))/7

    If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

    Appreciate your Kudos!!

     

7 Replies

  • VahidDM's avatar
    VahidDM
    Super User
    4 years ago

    Hi mb0307 

     

    Add a Custom column with this code in Power Query:

    (Date.StartOfWeek([Date])-Date.StartOfWeek(DateTime.Date(DateTime.LocalNow())))/7

    If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

    Appreciate your Kudos!!

     

    • romoguy15's avatar
      romoguy15
      Helper IV
      4 years ago

      Excellent solution, do you know how to change the number to be a whole number all within the same formula? Just to take out the the time zeros from the number

       

       

      • romoguy15's avatar
        romoguy15
        Helper IV
        3 years ago

        nevermind, i figured out. Was simple. Just add a data type in the custom column section.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Great solution. To set the right datatype in one line use this.

    = Table.AddColumn(Previous_M_Query_Step, "Name_Column", each
(Number.From((Date.StartOfWeek([Datum])-Date.StartOfWeek([Huidige_Datum]))/7)),Int64.Type)
  • peifc75's avatar
    peifc75
    Frequent Visitor
    2 years ago

    I have looked for this week offset everywhere. I was having a hard time figuring how to set it up. Thank you. Your solution really helped.

  • Jon_vB's avatar
    Jon_vB
    Advocate II
    2 years ago

    I needed the week offset where a week starts on Saturday.  Here's what I came up with - in case anyone else needs it (and - thanks to VahidDM for most of the work!)

     

    Number.From(Date.StartOfWeek([Date],Day.Saturday) - Date.StartOfWeek(Today,Day.Saturday))/7
     
    (the Number.From gets rid of the time formatting that romoguy15 points out)