Hi
I have a date table. I'd like to have Week Index column that increments by 1 every Wednesday and always has 7 days before changing (except for possibly the first entry dependant on what day the calendar starts
I've tried using Date.WeekOfYear([Date],Day.Wednesday) but this re-starts each year and I don't need it do that - it should just keep going
Eg
26/12/2018 53
27/12/2018 53
28/12/2018 53
29/12/2018 53
30/12/2018 53
31/12/2018 53
01/01/2019 1 <------ this should still be Week Index 53 - it being day 7 of the week number group
02/01/2019 54
03/01/2019 54
etc
The week number doesn't have to relate to a calendar week number ie if my calendar table starts on 01/07/2018 (Sunday) then it would look like:
01/07/2018 1
02/07/2018 1
03/07/2018 1
04/07/2018 2 <------ changes here as this is a Wednesday
etc
Help please
Hi Allison,
Add an Index column, starting from 1, then a column with this formula:
=Number.RoundUp([Index]/7,0)
Make sure your calendar starts on a Wednesday and everything will work as expected, this solution ignores the day of the week.
If you cannot set the start date to a wednesday, then you will have to perform a few extra steps:
-Add a new column named Custom.1: = Table.AddColumn(#"Added Custom", "Custom.1", each if Date.DayOfWeek([Date])=2 then [Custom] else null)
-Fill Up this new column: = Table.FillUp(#"Added Custom1",{"Custom.1"})
-The very last week might be missing values, this will fix the last week: = Table.TransformColumns(#"Filled Up", {{"Custom.1", each if _ = null then List.Max(#"Filled Up"[Custom]) else _, type number}})
See attached file.
Works perfect - thank you 
