Forum Discussion

bgierwi2's avatar
bgierwi2
Advocate I
7 months ago
Solved

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/ne...
Need Help
  • cengizhanarslan's avatar
    cengizhanarslan
    7 months ago

    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's avatar
Kedar_Pande
Super User
7 months ago

 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