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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
cjc322
Frequent Visitor

Creating a Fiscal Year & Fiscal Quarter in a DATE calendar

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

1 ACCEPTED SOLUTION
CheenuSing
Community Champion
Community Champion

@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.

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

View solution in original post

17 REPLIES 17
AnnetteSuh
Employee
Employee

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?

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

Anonymous
Not applicable

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] )

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
Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
Anonymous
Not applicable

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

tmears
Helper III
Helper III

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

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

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

THANK YOU!!!!!

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

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

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

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
Community Champion
Community Champion

@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.

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

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))).

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

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

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? 

Superb!! Thanx...:)..

@CheenuSing

 

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

Chris

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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