Forum Discussion

Yuiitsu's avatar
Yuiitsu
Helper V
6 years ago
Solved

IF statement for date

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?

 

 

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    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.

3 Replies

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    6 years ago

    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.