Forum Discussion

ScottBrown's avatar
ScottBrown
Icon for Helper II rankHelper II
1 year ago
Solved

Week Index Column for Power Query

I am trying to create a week index for Power Query. 

  • It would show something like this for the date range  ...-5, -4, -3, -2, -1, 0, 1, 2, 3, 4, 5... for all weeks in the date table.
  • My formula for the Week Index is below with the other related fields.
  • However it is caluclating an incorrect offset of 7. 

Any thoughts or if I am using the incorrect calculations?

 

M Code
StartOfCurrentWeek = Table.TransformColumnTypes(Table.AddColumn(#"Fiscal Year Status", "StartOfCurrentWeek", each Date.StartOfWeek(DateTime.FixedLocalNow(),+1)), {{"StartOfCurrentWeek", type date}}),
  StartOfWeek = Table.TransformColumnTypes(Table.AddColumn(StartOfCurrentWeek, "StartOfWeek", each Date.StartOfWeek([DateKey], 1)), {{"StartOfWeek", type date}}),
  #"Fiscal Week Index" = Table.TransformColumnTypes(Table.AddColumn(StartOfWeek, "Fiscal Week Index", each [StartOfWeek] - [StartOfCurrentWeek]), {{"Fiscal Week Index", Int64.Type}}),
Screenshot

 

 
Need Help
  • ZhangKun's avatar
    ZhangKun
    1 year ago

    7 or -7 is the difference in days, not weeks, so just divide by 7.

    #"Fiscal Week Index" = Table.TransformColumnTypes(
  Table.AddColumn(
    StartOfWeek, 
    "Fiscal Week Index", 
    each ([StartOfWeek] - [StartOfCurrentWeek]) / 7
  ),
  {{"Fiscal Week Index", Int64.Type}}
)

1 Reply

  • ZhangKun's avatar
    ZhangKun
    Icon for Super User rankSuper User
    1 year ago

    7 or -7 is the difference in days, not weeks, so just divide by 7.

    #"Fiscal Week Index" = Table.TransformColumnTypes(
  Table.AddColumn(
    StartOfWeek, 
    "Fiscal Week Index", 
    each ([StartOfWeek] - [StartOfCurrentWeek]) / 7
  ),
  {{"Fiscal Week Index", Int64.Type}}
)