Hi, thanks for reading.
I need a power query formula for MTD (month to date).
I would like to know if the date in the current month is prior to today.
IE I have a Power query table and would like to know all dates this month that are prior to today.
let
Source = Excel.CurrentWorkbook(){[Name="StartEndTable"]}[Content],
ChangedType = Table.TransformColumnTypes(Source,{{"Start Date", type date}
, {"End Date", type date}}),
CallCreateDateTable = CreateDateTable(ChangedType[Start Date]{0}
,ChangedType[End Date]{0}),
#"Changed Type" = Table.TransformColumnTypes(CallCreateDateTable,{{"Year", Int64.Type}, {"MonthNumberOfYear", Int64.Type}, {"DayOfMonth", Int64.Type}, {"DayOfWeekNumber", Int64.Type}}),
DatePlus1 = Table.AddColumn(#"Changed Type", "DatePlus1", each Date.AddDays([Date],-1)),
#"FINANCIAL MONTH" = Table.AddColumn(DatePlus1, "FINANCIAL MONTH", each if Date.Month([Date])>1 then Date.Month([Date])-1 else Date.Month([Date])+11),
#"FINANCIAL YEAR" = Table.AddColumn(#"FINANCIAL MONTH", "FINANCIAL YEAR", each if[FINANCIAL MONTH]=12 then [Year] -1 else [Year]),
#"CURRENT WEEK" = Table.AddColumn(#"FINANCIAL YEAR", "CURRENT WEEK", each Date.IsInCurrentWeek([DatePlus1])),
#"CURRENT MONTH" = Table.AddColumn(#"CURRENT WEEK", "CURRENT MONTH", each Date.IsInCurrentMonth([Date])),
#"CURRENT YEAR" = Table.AddColumn(#"CURRENT MONTH", "CURRENT YEAR", each Date.IsInCurrentYear([Date])),
#"Inserted Start of Week" = Table.AddColumn(#"CURRENT YEAR", "Start of Week", each Date.StartOfWeek([DatePlus1]), type date),
#"Removed DatePlus1" = Table.RemoveColumns(#"Inserted Start of Week",{"DatePlus1"}),
#"Duplicated Column1" = Table.DuplicateColumn(#"Removed DatePlus1", "FINANCIAL YEAR", "FINANCIAL YEAR - Copy"),
#"Duplicated Column" = Table.DuplicateColumn(#"Duplicated Column1", "FINANCIAL MONTH", "FINANCIAL MONTH - Copy"),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Duplicated Column", {{"FINANCIAL MONTH - Copy", type text}, {"FINANCIAL YEAR - Copy", type text}}, "en-CA"),{"FINANCIAL YEAR - Copy", "FINANCIAL MONTH - Copy"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"FINANCIAL YEAR SORT")
in
#"Merged Columns"
I need a power query formula for MTD (month to date).
I would like to know if the date in the current month is prior to today.
IE I have a Power query table and would like to know all dates this month that are prior to today.
let
Source = Excel.CurrentWorkbook(){[Name="StartEndTable"]}[Content],
ChangedType = Table.TransformColumnTypes(Source,{{"Start Date", type date}
, {"End Date", type date}}),
CallCreateDateTable = CreateDateTable(ChangedType[Start Date]{0}
,ChangedType[End Date]{0}),
#"Changed Type" = Table.TransformColumnTypes(CallCreateDateTable,{{"Year", Int64.Type}, {"MonthNumberOfYear", Int64.Type}, {"DayOfMonth", Int64.Type}, {"DayOfWeekNumber", Int64.Type}}),
DatePlus1 = Table.AddColumn(#"Changed Type", "DatePlus1", each Date.AddDays([Date],-1)),
#"FINANCIAL MONTH" = Table.AddColumn(DatePlus1, "FINANCIAL MONTH", each if Date.Month([Date])>1 then Date.Month([Date])-1 else Date.Month([Date])+11),
#"FINANCIAL YEAR" = Table.AddColumn(#"FINANCIAL MONTH", "FINANCIAL YEAR", each if[FINANCIAL MONTH]=12 then [Year] -1 else [Year]),
#"CURRENT WEEK" = Table.AddColumn(#"FINANCIAL YEAR", "CURRENT WEEK", each Date.IsInCurrentWeek([DatePlus1])),
#"CURRENT MONTH" = Table.AddColumn(#"CURRENT WEEK", "CURRENT MONTH", each Date.IsInCurrentMonth([Date])),
#"CURRENT YEAR" = Table.AddColumn(#"CURRENT MONTH", "CURRENT YEAR", each Date.IsInCurrentYear([Date])),
#"Inserted Start of Week" = Table.AddColumn(#"CURRENT YEAR", "Start of Week", each Date.StartOfWeek([DatePlus1]), type date),
#"Removed DatePlus1" = Table.RemoveColumns(#"Inserted Start of Week",{"DatePlus1"}),
#"Duplicated Column1" = Table.DuplicateColumn(#"Removed DatePlus1", "FINANCIAL YEAR", "FINANCIAL YEAR - Copy"),
#"Duplicated Column" = Table.DuplicateColumn(#"Duplicated Column1", "FINANCIAL MONTH", "FINANCIAL MONTH - Copy"),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Duplicated Column", {{"FINANCIAL MONTH - Copy", type text}, {"FINANCIAL YEAR - Copy", type text}}, "en-CA"),{"FINANCIAL YEAR - Copy", "FINANCIAL MONTH - Copy"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"FINANCIAL YEAR SORT")
in
#"Merged Columns"