Grouping Data within unique date range - Add Custom Column

[bgierwi2]

I have a data set, that I am looking to group

I want to add a new custom column to allow me to group data from a date range

I want to add a column so that all rows with the date 4/1/year to 3/31/next year will have the same unique name.

So for example:

Date	Car Number
12/10/2024	2
1/10/2025	4
5/25/2025	5
12/15/2025	4
1/6/2026	5
4/2/2027	7

this data will have a column added named "Grouping" where each row within that date range has the same name

Date	Car Number	Grouping
12/10/2024	2	2025
1/10/2025	4	2025
5/25/2025	5	2026
12/15/2025	4	2026
1/6/2026	5	2026
4/2/2027	7	2027

Everything between 4/1/2024 and 3/31/2025 is grouped together in the group "2025"

Everything between 4/1/2025 and 3/31/2026 is grouped together in the group "2026"

Everything between 4/1/2026 and 3/31/2027 is grouped together in the group "2027"

Everything between 4/1/2027 and 3/31/2028 is grouped together in the group "2028"

It doesnt matter what the name of the group is, it just needs to be unique.

And this data will have data added to it, so any grouping will need to generate a new name past 2026, 2027, 2028, 2029 etc.

This name will just be used so the data can be grouped for further calculations

[cengizhanarslan · Accepted solution]

If you need to do it within Power BI, you could create a calculated column as below:

Grouping =
IF ( MONTH ( 'Table'[Date] ) >= 4, YEAR ( 'Table'[Date] ) + 1, YEAR ( 'Table'[Date] ) )

OR

You could also make the same calculation with Power Query as below:

= Text.From(if Date.Month([Date]) >= 4 then Date.Year([Date]) + 1 else Date.Year([Date]))

[Kedar_Pande]

 Select table → Add Column → Custom Column

let
FiscalYearStart = #date(Date.Year([Date]) - if Date.Month([Date]) < 4 then 1 else 0, 4, 1),
FiscalYear = Number.ToText(Date.Year(FiscalYearStart))
in
FiscalYear

If this answer helped, please click 👍 or Accept as Solution.
-Kedar
LinkedIn: https://www.linkedin.com/in/kedar-pande

@bgierwi2

[rohit1991]

Hii @bgierwi2 

To group dates into a unique April–March range, create a fiscal-year column based on the month of the date. The correct logic is: if the date’s month is April (4) or later, assign the grouping as Year + 1; otherwise assign the Year. This ensures that all dates from 4/1/2024 to 3/31/2025 map to 2025, 4/1/2025 to 3/31/2026 map to 2026, and so on, and it automatically continues for future years without any manual changes.