IF statement for date

[Yuiitsu]

Hi Expert

I need to create a new column using IF statement to calculate my finance year.

E.g: if date falls between April 2019 to March 2020 = FY 20

if date falls between April 2018 to March 2019 = FY 19

Can anyone help me with this statement?

[v-alq-msft · Accepted solution]

Hi, @Yuiitsu 

Based on your description, I created data to reproduce your scenario.

Table:

Table = CALENDAR(DATE(2018,1,1),DATE(2020,12,31))

You may create a calculated column as below.

finance year = 
var _date = 'Table'[Date]
var _month = MONTH(_date)
var _year = YEAR(_date)

return
IF(
    _month>=4&&_month<=12,
    CONCATENATE("FY",VALUE(FORMAT(_date,"YY"))+1),
    IF(
        _month<=3&&_month>=1,
        CONCATENATE("FY",VALUE(FORMAT(_date,"YY"))+0)
    )
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @Yuiitsu 

Please check the link            Fiscal year Calculated column 

Cheers

CheenuSing

[amitchandak]

@Yuiitsu 

Create an end of year and use

FY = "FY" & format(endofyear([date],"3/31"),"YY")

https://docs.microsoft.com/en-us/dax/endofyear-function-dax

You also have startofyear. That also takes the end date as parameter, if needed