Forum Discussion

M_SBS_6's avatar
M_SBS_6
Helper V
1 year ago
Solved

Financial Year

Hi, is there anyway to create a financial year column that updates automatically going forward? 

Ideally, I'd like a column where is the date is between 1st April 2023 and 31st March 2024 to output 23/24. Then 1st April 2024 to 31st March 2025 to output 25/25 but then for logic to update next years automatically? 

Need Help
  • ryan_mayu's avatar
    ryan_mayu
    1 year ago

    M_SBS_6 

    you can try this to create a column

     

    FY = if(month('Table'[Date]) in {1,2,3},right(year('Table'[Date])-1,2)&"/"&right(year('Table'[Date]),2),right(year('Table'[Date]),2)&"/"&right(year('Table'[Date])+1,2))
     
     
  • danextian's avatar
    danextian
    1 year ago

    Hi M_SBS_6 

    Try this:

    FY = 
VAR _qtr =
    QUARTER ( dates[date] )
VAR _calendaryear =
    YEAR ( dates[date] )
VAR _yr =
    IF ( _qtr <= 1, _calendaryear - 1, _calendaryear ) -- Adjust fiscal year to the previous year for Q1 (Jan-Mar)
RETURN
    RIGHT ( _yr, 2 ) & "/"
        & RIGHT ( _yr + 1, 2 )

     

2 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    1 year ago

    M_SBS_6 

    you can try this to create a column

     

    FY = if(month('Table'[Date]) in {1,2,3},right(year('Table'[Date])-1,2)&"/"&right(year('Table'[Date]),2),right(year('Table'[Date]),2)&"/"&right(year('Table'[Date])+1,2))
     
     
  • danextian's avatar
    danextian
    Super User
    1 year ago

    Hi M_SBS_6 

    Try this:

    FY = 
VAR _qtr =
    QUARTER ( dates[date] )
VAR _calendaryear =
    YEAR ( dates[date] )
VAR _yr =
    IF ( _qtr <= 1, _calendaryear - 1, _calendaryear ) -- Adjust fiscal year to the previous year for Q1 (Jan-Mar)
RETURN
    RIGHT ( _yr, 2 ) & "/"
        & RIGHT ( _yr + 1, 2 )