Forum Discussion
Grouping Data within unique date range - Add Custom Column
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
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]))
3 Replies
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]))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.
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
FiscalYearIf this answer helped, please click 👍 or Accept as Solution.
-Kedar
LinkedIn: https://www.linkedin.com/in/kedar-pande
