cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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
Microsoft

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
Microsoft

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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors