Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Prepping for a Fabric certification exam? Join us for a live prep session with exam experts to learn how to pass the exam. Register now.

Reply
WillTickel
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
v-ljerr-msft
Microsoft Employee
Microsoft Employee

Hi @WillTickel,

 

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

 

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

View solution in original post

8 REPLIES 8
v-ljerr-msft
Microsoft Employee
Microsoft Employee

Hi @WillTickel,

 

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

 

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 )
 
Please help!
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)

 

Thx

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

dedelman_clng
Community Champion
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 ( 201641 ),
        Dates[Dates] <= DATE ( 2017331 )
    ),
    "FY16/17",
    IF (
        AND (
            Dates[Dates] >= DATE ( 201741 ),
            Dates[Dates] <= DATE ( 2018331 )
        ),
        "FY17/18",
        IF (
            AND (
                Dates[Dates] >= DATE ( 201841 ),
                Dates[Dates] >= DATE ( 2019331 )
            ),
            "FY18/19",
            0
        )
    )
)

 

Hope this helps,

David

RMDNA
Solution Sage
Solution Sage

Could you post the error message?

Helpful resources

Announcements
PBIApril_Carousel

Power BI Monthly Update - April 2025

Check out the April 2025 Power BI update to learn about new features.

Notebook Gallery Carousel1

NEW! Community Notebooks Gallery

Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.

April2025 Carousel

Fabric Community Update - April 2025

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