cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Fiscal Year Calculated Columns

I'm certain there's a more elegant (and correct) way of doing this, but I'm trying to create a calculated column which gives each date a corresponding fiscal year label e..g "FY16/17"

The formula I'm curently working on is

Financial Year = IF(DATESBETWEEN, Dates[Dates], DATE(2016,4,1) , DATE (2017,3, 31)) , "FY16/17",

IF(DATESBETWEEN, Dates[Dates], DATE(2017,4,1) , DATE (2018,3, 31)) , "FY17/18",

IF(DATESBETWEEN, Dates[Dates], DATE(2018,4,1) , DATE (2019,3, 31)) , "FY18/19",  0)

It keeps responding with an error message, can anyone guide me in the right direction?

1 ACCEPTED SOLUTION
Microsoft Employee

Hi @WillTickel,

Based on my test, the formula below should also work in your scenario.

```Financial Year =
VAR fy =
IF (
MONTH ( 'Dates'[Dates] ) <= 3,
VALUE ( FORMAT ( 'Dates'[Dates], "YY" ) ) - 1,
VALUE ( FORMAT ( 'Dates'[Dates], "YY" ) )
)
RETURN
CONCATENATE ( "FY", CONCATENATE ( fy, CONCATENATE ( "/", fy + 1 ) ) )
```

Regards

8 REPLIES 8
Microsoft Employee

Hi @WillTickel,

Based on my test, the formula below should also work in your scenario.

```Financial Year =
VAR fy =
IF (
MONTH ( 'Dates'[Dates] ) <= 3,
VALUE ( FORMAT ( 'Dates'[Dates], "YY" ) ) - 1,
VALUE ( FORMAT ( 'Dates'[Dates], "YY" ) )
)
RETURN
CONCATENATE ( "FY", CONCATENATE ( fy, CONCATENATE ( "/", fy + 1 ) ) )
```

Regards

Anonymous
Not applicable

I have written this code and am getting the error - Cannot convert value " of type text to type number.

Fiscal Year =
VAR FY =
IF (
MONTH ( Table1[DATE] ) <= 3,
VALUE ( FORMAT ( Table1[Date], "YY" ) ) - 1,
VALUE ( FORMAT ( Table1[Date], "YY" ) )
)
RETURN
CONCATENATE ( "FY ", FY + 1 )

Anonymous
Not applicable

Figured it out - the code works! However there was a blank cell in the [Date] column - it has over 5 years of data so yeah!

Switch can also do the same:

SWITCH(TRUE(),

Month([Date])<4,YEAR([Date])-1 &"-"&YEAR([Date]),

YEAR([Date])&"-"&YEAR([Date])+1)

Resolver I

Thx

Frequent Visitor

Many Thanks @v-ljerr-msft. It worked perfectly!

Community Champion

DATESBETWEEN is a function that returns a table, not a true/false as to whether or not a date falls between two dates

I think this should work for what you're trying to do:

```Financial Year =IF (    AND (        Dates[Dates] >= DATE ( 2016, 4, 1 ),        Dates[Dates] <= DATE ( 2017, 3, 31 )    ),    "FY16/17",    IF (        AND (            Dates[Dates] >= DATE ( 2017, 4, 1 ),            Dates[Dates] <= DATE ( 2018, 3, 31 )        ),        "FY17/18",        IF (            AND (                Dates[Dates] >= DATE ( 2018, 4, 1 ),                Dates[Dates] >= DATE ( 2019, 3, 31 )            ),            "FY18/19",            0        )    ))
```

Hope this helps,

David

Solution Sage

Could you post the error message?

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors