Page 1 of 1

Get start and end date of the current and last semester of the calendar year

Posted: Sun Feb 11, 2018 9:27 am
by okat
From an X date, I am interested in finding ("M") the starting and ending date of the current semester and the starting and ending date of the previous semester, of the calendar year. For example, today is 10/02/2018. Start current semester = 01/01/2018, date of completion of the current semester = 30/06/2018, start date of last semester = 01/07/2017, last semester's final date = 31/12/2017.
Thank you.

Re: Get start and end date of the current and last semester of the calendar year

Posted: Tue Feb 27, 2018 10:19 pm
by Rudi
A very rudimentary way...

I started with a single column table with a column of dates (with heading "Order Date"), and built the rest of the output from those dates.

Code: Select all

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Order Date", type date}}),
    #"Inserted Start of Year" = Table.AddColumn(#"Changed Type", "Start current semester", each Date.StartOfYear([Order Date]), type date),
    #"Inserted Year" = Table.AddColumn(#"Inserted Start of Year", "Year", each Date.Year([Start current semester]), type number),
    #"Inserted Month" = Table.AddColumn(#"Inserted Year", "Month", each 6, type number),
    #"Inserted Day" = Table.AddColumn(#"Inserted Month", "Day", each 1, type number),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Inserted Day", {{"Year", type text}, {"Month", type text}, {"Day", type text}}, "en-US"),{"Year", "Month", "Day"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"Merged"),
    #"Renamed Columns" = Table.RenameColumns(#"Merged Columns",{{"Merged", "End current semester"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"End current semester", type date}}),
    #"Inserted Year1" = Table.AddColumn(#"Changed Type1", "Year", each Date.Year([Order Date])-1, type number),
    #"Added Custom" = Table.AddColumn(#"Inserted Year1", "Custom", each 7),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each 1),
    #"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Added Custom1", {{"Year", type text}, {"Custom", type text}, {"Custom.1", type text}}, "en-ZA"),{"Year", "Custom", "Custom.1"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"Merged"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Merged Columns1",{{"Merged", type date}}),
    #"Inserted Year2" = Table.AddColumn(#"Changed Type2", "Year", each Date.Year([Order Date])-1, type number),
    #"Added Custom2" = Table.AddColumn(#"Inserted Year2", "Custom", each 12),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom.1", each 31),
    #"Merged Columns2" = Table.CombineColumns(Table.TransformColumnTypes(#"Added Custom3", {{"Year", type text}, {"Custom", type text}, {"Custom.1", type text}}, "en-ZA"),{"Year", "Custom", "Custom.1"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"Merged.1"),
    #"Changed Type3" = Table.TransformColumnTypes(#"Merged Columns2",{{"Merged.1", type date}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Changed Type3",{{"Merged", "Start previous semester"}, {"Merged.1", "End previous semester"}})
in
    #"Renamed Columns1"