Power Query Start of Month - Not True Start of Month

[c_cook28954]

I'm sure other users are already aware of this but, if not, it looks like the Start of Month function in Power Query is based on a four day period (starts on Monday, ends on Sunday).

This means that, unless a date starts exactly on a Monday, the Start of Month function will produce the previous months date for the first (7-{insert day of week]) which can be misleading when you're performing calculations - sample below: 

If you're looking for the start of the specific month you're analyzing, it's more accurate to create a calculated column based on the DAX function STARTOFMONTH as this produces the first value of the month in question. 

[collinsg · Accepted solution]

Good day c_cook28954,

From your screen shot I interpret the first column as the date which is the starting point for calculating the other columns. The second column looks like "Start of Week". The third column looks like it is the one you wish to be "Start of Month". However it looks to me as if this column is the start of the month for the start of week column. Could it just be a "typo" that has resulted in the your column (rather than your first) being the argument of your start of month function? I reproduced your first column and added columns for start of week, start of month and start of month of start of week. Everything behaved correctly as per the screen shot below.

let
   Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
   #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),

   #"Add Start of Week" = Table.AddColumn(
      #"Changed Type", 
      "Start of Week", 
      each Date.StartOfWeek( [Date] ), 
      type date),
   #"Add Start of Month" = Table.AddColumn(
      #"Add Start of Week", 
      "Start of Month", 
      each Date.StartOfMonth( [Date] ), 
      type date),
   #"Add Start of Month of Start of Week" = Table.AddColumn(
      #"Add Start of Month", 
      "Start of Month of Start of Week", 
      each Date.StartOfMonth( [Start of Week] ), 
      type date)
in
   #"Add Start of Month of Start of Week" 

[c_cook28954]

You're right - looked back at my advanced editor and I had chosen Start of Week by mistake when creating my start of month column. 

I'd still naturally expect that a start of Month function applied to a Start of Week date would produce a true Start of Month date and not one adjusted to look at the first Monday of the 30 day period and trace it back to a different month. 

Appreciate the help anyway - interesting to know for future reference