Week offset in Power Query

[mb0307]

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 

[Jon_vB]

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)

[peifc75]

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.

[Anonymous]

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)

[VahidDM · Accepted solution]

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]

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]

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

[mb0307]

Worked perfectly.  Thanks