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

okat
Posts: 16
Joined: Tue Apr 18, 2017 4:04 am

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

Postby okat » Sun Feb 11, 2018 9:27 am

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.

Rudi
Posts: 6
Joined: Fri Sep 01, 2017 4:33 pm

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

Postby Rudi » Tue Feb 27, 2018 10:19 pm

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"


Return to “Power Query”

Who is online

Users browsing this forum: No registered users and 1 guest

cron