Creating a Fiscal Year & Fiscal Quarter in a DATE calendar

[cjc322]

Hello,

I have the following date table that I use as a 'StartDate' & 'EndDate'. Here is the formula that creates the date table currently:

StartDate = ADDCOLUMNS(CALENDAR("1-1-2013","31-12-2017"),"DateAsInteger",FORMAT([date],"YYYYMMDD"),"Year",YEAR([Date]),"Monthnumber",FORMAT([Date],"MM"),"YearMonthNumber",FORMAT([Date],"YYYY/MM"),"YearMonthShort",FORMAT([Date],"YYYY/mmm"),"MonthNameShort",FORMAT([Date],"mmm"),"MonthNameLong",FORMAT([Date],"mmmm"),"DayOfWeekNumber",WEEKDAY([Date]),"DayOfWeek",FORMAT([Date],"dddd"),"DayOfWeekShort",FORMAT([Date],"ddd"),"Quarter","Q" & FORMAT([Date],"Q"),"YearQuarter",FORMAT([Date],"YYYY" & "/" & FORMAT ([Date],"Q")) )

I would like to add in Fiscal Year and Fiscal Quarter.

The Fiscal Year begins July 1 and ends June 30. 

I would like to display the Fiscal Year as "FY13-14" for (July 1, 2013 thru June 30, 2014) and etc. and the Fiscal Quarter I would like to display as "FQ1" "FQ2" etc.

Can anyone help me provide the lines I would add to the above formula to achieve this?

Thank you in advance!

Chris

@cjc322

[AnnetteSuh]

Hi all! I am new to PowerBI, where do you put this code in to get it to work? I'm trying to do a fiscal year that starts July 1st. I only have one table in my dashboard, can someone tell me how to append the one table rather than having to do a second table?

[hrathee]

I have the exact same problem. Would love know this.

[Anonymous]

Hello all. This is the code I use to create a table, but my quarters are wrong. My fiscal year starts Oct. 1 and my Quarters should also start Oct. 1 but it says Q4 instead of Q5. Is this table correct for the fiscal year and how do I modifiy the Quarters to match with the fiscal year start? Thanks

Calendar =

ADDCOLUMNS (

    ADDCOLUMNS (

        CALENDAR ( DATE ( 2019, 10, 1 ), DATE ( 2020, 10, 1 ) ),

        "Year", YEAR ( [Date] ),

        "MonthNameShort", FORMAT ( [Date], "mmm" ),

        "MonthNumber", MONTH ( [Date] ),

        "Quarter", "Q" & FORMAT ( [Date], "Q" ),

        "YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" )

    ),

    "FY", IF ( [MonthNumber] >= 10, [Year] + 1, [Year] )

[CheenuSing]

Hi @Anonymous ,

Try this

ADDCOLUMNS (

ADDCOLUMNS (

ADDCOLUMNS (

    ADDCOLUMNS (

        CALENDAR ( DATE ( 2019, 10, 1 ), DATE ( 2020, 10, 1 ) ),

        "Year", YEAR ( [Date] ),

        "MonthNameShort", FORMAT ( [Date], "mmm" ),

        "MonthNumber", MONTH ( [Date] ),

        "Quarter", "Q" & FORMAT ( [Date], "Q" ),

        "YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" )

    ),

    "FY", IF ( [MonthNumber] >= 10, [Year] + 1, [Year] ),

    "FiscalMonth",If( [MonthNumber] >= 10  , [MonthNumber]  - 9,[MonthNumber] +3 )

),

    "FiscalQuarterNumber",  ROUNDUP ([FiscalMonth]/3,0) )

,

    "FiscalQuarterDisplay", "Q" & format([FiscalQuarterNumber],"0") ,

        "FiscalYearQuarter", FORMAT ( [FY], "####" ) & "/Q" & FORMAT ( [FiscalQuarterNumber], "#" )

)

I added FY  (Fiscal year), FiscalMonth then added FiscalQuarterNumber and finally added

FiscalQuarterDisplay and FiscalYearQuarter.

Cheers

CheenuSing

[Anonymous]

Worked like a charm. I will mark your post as a solution, once I figure out how. Thanks again!!!

[tmears]

Hi

I have followed the instructions by @cjc322 which worked a charm, thank you so much.  I would like to take this to the next stage and would like to add a calcualted colum, which flags if today date in within the present Fiscal quarter. i have used to show current month :

IsCurrentMonth =
    IF (
        YEAR ( DateCalendar[Date] ) = YEAR ( TODAY () )
            && MONTH ( DateCalendar[Date] ) = MONTH ( TODAY () ),
        "Yes",
        "No"
    )

But struggling with current FIscal Month, and therefore fiscal year, mu fiscal year starts april

any help would be much appriciated

TIm

[CheenuSing]

Hi @tmears

Please try the following

1. Create a column in your DateTable

    FiscalYearMonthNumber = DateTable[FiscalYearNumber]*100+DateTable[FiscalMonth]

2. Create a measure called CurFiscalYear

    CurFiscalYear = If( Month(Today()) >=4 , Year(Today()),Year(Today() -1 )

3. Create a measure called CurFiscalMonth

    CurFiscalMonth = If( Month(Today()) >= 4 , Month(Today()) -4,Month(Today()) + 4 )

4. Create a measure called

    CurFiscalQr =ROUNDUP ([CurFiscalMonth]/3,0)

5. Create a measure called

    CurFiscalYearMonth = [CurFiscalYear]*100 + [CurFiscalMonth]

6. Create a Column in DateTable 

    IsCurMonth = if(DateTable[FiscalYearMonthNumber] = [CurFiscalMonth],"Yes","No")

 7. Create a column in DateTable

     FiscalYearQuarterNumber = DateTable[FiscalYear] *100 + DateTable[FiscalQuarterNumber]

8. Create a measure called

     CurFiscalYearQuarterNumber =  [CurFiscalYear]*100 + [CurFiscalQr]

9. Create a Column in DateTable 

    IsCurQr = if(DateTable[FiscalYearQuarterNumber ] = [CurFiscalYearQuarterNumber],"Yes","No")

10. Create a column in DateTable

    IsCurYear = if(DateTable[FiscalYear] = [CurFiscalYear],"Yes","No")

You can use these columns IsCurQr, IsCurMonth,IsCurYear in your filter expressions.

If this works for you please accept it as a solution and also gice KUDOS.

Cheers

CheenuSing

ChennuSing

[Britknee30]

THANK YOU!!!!!

[tmears]

well we are nearly there... however the iscurmonth, iscurqr and is curyear is showing No for all records

[CheenuSing]

Hi @tmears

Can you share your date table in dropbox or onedrive and provide the link.

How is your datetable created, the script for that.

Cheers

CheenuSing

[panipat1990]

Dear Sir,

i Created This But I want to This Calendar Table Join to Transaction Table.How is it Possible.

Because this table is not Seeing in Query Editor.Please Help me

[CheenuSing · Accepted solution]

@cjc322

For the fiscal year 

1. Create a FiscalYearNumber  column as

FiscalYearNumber=If( Month([Date]) >= 7  , Year([Date]),Year([Date]) -1 )

FiscalYearDisplay = ="FY"&Right(Format([FiscalYearNumber],"0#"),2)&"-"&Right(Format([FiscalYearNumber]+1,"0#"),2)

Create a column called FiscalMonth 

FiscalMonth=(If( Month([Date]) >= 7  , Month([Date]) - 6,Month([Date]) + 6 )

Now FiscalQuarterNumber =  ROUNDUP ([FiscalMonth]/3,0)

FiscalQuarterDisplay= "FQ" & format([FiscalQuarterNumber],"0") 

 Try it out.

If it works please accept this as a solution and also give Kudos.

[charerusu]

Hi @CheenuSing 

How if i want to add 1 column to show and categories what fiscal year by month

like

April 1, 2023 - March 31, 2024 = FY24

April 1, 2024 - March 31, 2025 = FY25

April 1, 2025 - March 31, 2026 = FY26

.......

.......

thank's and appreciate

[Allan77R2V1]

I am having problems with the line below. Do both lines get entered when creating the column? When I try to do this I get the syntax error which is alos below. If Have the just the first line it works fine but the second line is giving the syntax error.

FiscalYearNumber=If( Month([Date]) >= 3  , Year([Date]),Year([Date]) -1 )
FiscalYearDisplay = ="FY"&Right(Format([FiscalYearNumber],"0#"),2)&"-"&Right(Format([FiscalYearNumber]+1,"0#"),2)

The syntax for '=' is incorrect. (DAX(="FY"&Right(Format([FiscalYearNumber],"0#"),2)&"-"&Right(Format([FiscalYearNumber]+1,"0#"),2))).

[CheenuSing]

Hi @Allan77R2V1,

In the sceond formula, the two "=" sign should be replaced with one "=" sign.  Typo error.

Thanks for pointing out. Appreciate that.

Cheers

CheenuSing

[Jo-Jo_UK]

Feeling a little late to the party here but wondering if anyone can help:

I am trying to apply this code:

FiscalYearNumber = If( Month([Date]) >= 3 , Year([Date]),Year([Date]) -1 )

FiscalYearDisplay = "FY"&Right(Format([FiscalYearNumber],"0#"),2)&"-"&Right(Format([FiscalYearNumber]+1,"0#"),2)

But still getting this error: The syntax for 'FiscalYearDisplay' is incorrect. (DAX(If( Month([Date]) >= 3 , Year([Date]),Year([Date]) -1 )FiscalYearDisplay = "FY"&Right(Format([FiscalYearNumber],"0#"),2)&"-"&Right(Format([FiscalYearNumber]+1,"0#"),2))).

What am i missing? 

[arpitkumar2310]

Superb!! Thanx...:)..

[cjc322]

@CheenuSing

Just created and tested. Your logic works. Thank you very much!

Chris