Forum Discussion

ArchStanton's avatar
ArchStanton
Power Participant
3 years ago
Solved

Date Formatting

Hi,

 

I want to have my Financial Year formatted like this = 2022-23 instead of 2022-2023

 

I currently have it like this in my date table = 'Date'[Year] & "-" & 'Date'[Next Year] = 2022-2023

 

I have a Next Year Column but when I combine them everything after the "-" is 05?

 

Fin Year = 'Date'[Year] & "-" & FORMAT('Date'[Next Year], "YY")
 
Any ideas anyone?
 
Thanks,
 
 
 
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Assuming 'Date'[Year] is the start year of the financial year this would work.

     

    FY new =
VAR trimmed =
    RIGHT ( 'Date'[Year] + 1, 2 )
RETURN
    'Date'[Year] & "-" & trimmed

     

     You could replace 'Date'[Year] +1 with the column 'Date'[Next Year] if that is the end year of each financial year

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Assuming 'Date'[Year] is the start year of the financial year this would work.

     

    FY new =
VAR trimmed =
    RIGHT ( 'Date'[Year] + 1, 2 )
RETURN
    'Date'[Year] & "-" & trimmed

     

     You could replace 'Date'[Year] +1 with the column 'Date'[Next Year] if that is the end year of each financial year

  • Syk's avatar
    Syk
    Resident Rockstar
    3 years ago

    You can just get the last 2 digits from your next year column. 

    Fin Year = 'Date'[Year] & "-" & RIGHT(YEAR('Date'[Next Year]),2)

     

    • ArchStanton's avatar
      ArchStanton
      Power Participant
      3 years ago

      thanks but that gives me the 05 problem i described in the original message. The VAR option below works though - thanks for responding!