Code for custom year shows the wrong result

[abfdjuk]

Hi All

I have created a simple code to say if the month is september or earlier and the date is 14th or earlier then show Year -1 and if greater than show year. BUT the year shows correctly for dates before but doesn't seem to work for dates after. Where am I going wrong?

TEST Year =

VAR FY =

IF (MONTH( ( 'Date'[Date] ) <= 9 && DAY ( 'Date'[Date] ) <=14),

VALUE ( FORMAT ( 'Date'[Date], "YY" ) )-1 ,

VALUE ( FORMAT ( 'Date'[Date], "YY" ) ))RETURN

CONCATENATE ( "", FY )

[PaulDBrown · Accepted solution]

I'm not too sure of the logic here. What you are saying is that 13/september/2022 is FY 2021, but the 20/august/2022 is FY 2022?

If so , try:

FY =
IF (
    AND ( MONTH ( fTable[Date] ) < 10, DAY ( fTable[Date] ) < 15 ),
    YEAR ( fTable[Date] ) - 1 & " FY",
    YEAR ( fTable[Date] ) & " FY"
)

If the cut-off date is the 14 september (so dates before are the previous year and dates after are the current year, then try:

FY 14 sep = 
VAR _Date = DATE(YEAR('Date'[Date]), 9, 15)
RETURN
IF (
    'Date'[Date] < _Date,
    YEAR ( 'Date'[Date] ) - 1 & " FY",
    YEAR ( 'Date'[Date] ) & " FY"
)

[abfdjuk]

Great thanks. The odd start dates correlates to the agricultural year in the UK.

[abfdjuk]

How do I display the year as just the two last numbers?

[PaulDBrown]

Use the RIGHT functio:

FY Short = 
IF (
    AND ( MONTH ( 'Date'[Date] ) < 10, DAY ( 'Date'[Date] ) < 15 ),
    RIGHT(YEAR ( 'Date'[Date] ) - 1, 2) & " FY",
    RIGHT(YEAR ( 'Date'[Date] ), 2) & " FY"
)