Skip to main content
cancel
Showing results for 
Search instead 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

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
Sept PBI Carousel

Power BI Monthly Update - September 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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