cancel
Showing results for 
Search instead for 
Did you mean: 
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

15 REPLIES 15
AnnetteSuh
Microsoft
Microsoft

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?

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

CheenuSing
Community Champion
Community Champion

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

CheenuSing
Community Champion
Community Champion

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!

View solution in original post

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!

Superb!! Thanx...:)..

@CheenuSing

 

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

Chris

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors