Custom time period and variation

[Rachitik67]

Hi, i am working in the education field.

We have 4 types of time periods :

1- Academic year : Ex: Academic year 2021 is from september 2021 to august 2022

2- Administrative year : Ex : Administrative year 2021 is from may 2021 to april 2022

3- Civil year : 2021 is from january 2021 to december 2021

4 Semesters :

Examples -> Winter 2021 is from january 2021 to april 2021, Summer 2021 is from may 2021 to august 2021 and winter 2021 is from september 2021 to december 2021.

I need to get the number of distinct students and the variation each year for each type of periods. the data is extracted every semester. That is why i need the distinct count of the students. Every year, the data for the new year will be added, so the calculation needs to be dynamic.

How can i create the custom time periods and how can i calculate the variation each year for the differents time periods?

Here is a sample of my data :

Date	ID	Program - cd
2020-01-01	2934321	256123
2020-09-01	2934321	256123
2021-05-01	2934321	256123
2020-09-01	4558209	169842
2020-09-01	3243669	169842
2021-05-01	3243669	169842
2021-01-01	3250377	365984
2021-12-01	3250377	245876
2020-05-01	3114093	163988
2021-05-01	2841033	163988
2020-09-01	5352171	396668
2022-01-01	3114093	163988
2022-05-01	2841033	145896
2022-09-01	5352171	396668

[amitchandak · Accepted solution]

@Rachitik67 , You can get different method to create year based on any start date

Creating Financial Calendar - From Any Month
https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calend...
https://medium.com/chandakamit/cheat-sheet-calendar-of-any-standard-month-just-one-variable-apart-5e...
https://amitchandak.medium.com/cheat-sheet-power-query-financial-year-calendar-5ceaacb520f1
DAX Calendar - Standard Calendar, Non-Standard Calendar, 4-4-4 Calendar
https://www.youtube.com/watch?v=IsfCMzjKTQ0&t=145s
Power BI Date Table: https://www.youtube.com/watch?v=cN8AO3_vmlY&t=18180s

new columns 

Year Start Date Sep = Date(if(Month([Date]) <9, Year([Date])-1, Year([Date]) ), 9,1 )

Year Start Date May = Date(if(Month([Date]) <5, Year([Date])-1, Year([Date]) ), 5,1 )

Semester = Quotient( month([Date]) -1, 4) +1

Year Semester  = Year([Date])*100 +  (Quotient( month([Date]) -1, 4) +1 )

Semester Rank = Rankx(Date, [Year Semester],,asc,dense)

Measure to check data this vs last Semester 

This Semester = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Semester Rank]=max('Date'[Semester Rank])))
Last Semester = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Semester Rank]=max('Date'[Semester Rank])-1))

[Rachitik67]

@amitchandak  Thanks for the reply. I did not get me exactly where i wanted, but it gave me the ideas needed to do what i wanted.