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

[Aquila]

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.

 

[GraH - Guido]

you can do this to find semesters of dates:

let
  Source = Excel.CurrentWorkbook(){[Name="tDates"]}[Content],
  #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
  StartOfSemester = Table.AddColumn(#"Changed Type", "Start Of Semester", each if Date.QuarterOfYear([Date]) = 1 or Date.QuarterOfYear( [Date] ) = 3 then Date.StartOfQuarter([Date]) else
Date.StartOfMonth(Date.AddQuarters(Date.StartOfQuarter([Date]),-1))),
  EndOfSemester = Table.AddColumn(StartOfSemester, "End Of Semester", each if Date.QuarterOfYear([Date]) = 2 or Date.QuarterOfYear( [Date] ) = 4 then Date.EndOfQuarter([Date]) else
Date.EndOfMonth(Date.AddQuarters(Date.EndOfQuarter([Date]),1))),
  StartOfPrevSemester = Table.AddColumn(EndOfSemester, "Start Of Previous Semester", each Date.AddQuarters([Start Of Semester],-2)),
  EndOfPrevSemester = Table.AddColumn(StartOfPrevSemester, "End Of Previous Semester", each Date.AddQuarters([End Of Semester],-2)),
  #"Changed Type1" = Table.TransformColumnTypes(EndOfPrevSemester,{{"Start Of Semester", type date}, {"End Of Semester", type date}, {"Start Of Previous Semester", type date}, {"End Of Previous Semester", type date}})
in
  #"Changed Type1"

 

[Aquila]

Thank a lot!!! Very well accomplished. I have only modified "End Of Previus Semester": Date.EndOfMonth (date. AddQuarters ([End Of Semester],-2)), because in some cases it took as end of the semester "30/12/2017".
Regards.