May 6, 2022
September17th, till January16th, display=> 1st term
From January17th till June16th display=>2nd term
From June17th till September 16th display=>3rd term
September 17th=> 1st term..(Cycle begins).
I wish this to be automated in powerquery.
January 31, 2022
Can you clarify why a start date if 17 Jan 2023 would become the 2nd term with an enrolment id for the years 2023/2024? Shouldn't that be the 2nd term for the 2022/2023 season?
And then you mention to be working with 3 terms, each containing ~4 months. Then, I assume that the 2nd term starts the 17th of May. Not the 17th of June.
If all of this is correct, please have a look at the attached file. I added two more employees to test the solution. Note that I added a small table indicating the Term for each the months, where January, May and September may fall in two terms, depending on the day of the month.
The queries are all very basic and I did not attempt to make it fancy. All was done via the User Interface. No manipulation of the underlying M-code was needed.