cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cathoms
Resolver II
Resolver II

How do I create custom fiscal year, quarter, and period columns?

Hi, I am new to Power BI. I am setting up a Date_Lookup table and trying to create a Fiscal Year column based on a Date column (Date format is MM/DD/YEAR so January 11, 2020 looks like 1/11/2020). I've been searching these forums and tried a couple of formulas but apparently I don't understand them enough to get it right. Below is a screen shot of my attempt at making the custom column. Note that I clicked "Show error" and the _ in Date_Lookup was highlighted.

 

cathoms_0-1603402766473.png

 

Here is the formula I am trying: Fiscal Year = If(MONTH('Date_Lookup'[Date])>6,YEAR('Date_Lookup'[Date])+1,YEAR('Date_Lookup'[Date]))

 

Once I get the Fiscal Year column I would then like to generate a fiscal year quarter column and then a fiscal year period column. Our fiscal year runs from July 1 to June 30. Fiscal year period is the number of the month in the fiscal year, so July is FY period 1 and June is FY period 12.

 

Any help is much appreciated!

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

Hi,

That formula is correct but is a DAX formula.  That has to be written as a calculated column formula.  You are incorrectly writing that in the Query Editor.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

8 REPLIES 8
amitchandak
Super User
Super User

@cathoms , if you want in M then refer

https://radacad.com/create-a-date-dimension-in-power-bi-in-4-steps-step-2-fiscal-columns

 

For Dax refer to my blog, for any of the 12-month calendar

https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calend...



New Power BI Features
Datamarts: https://youtu.be/8tskWsJTEpg
Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin !! Proud to be a Super User!
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
!! Subscribe to my youtube Channel !!
Ashish_Mathur
Super User
Super User

Hi,

That formula is correct but is a DAX formula.  That has to be written as a calculated column formula.  You are incorrectly writing that in the Query Editor.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Oops! Thanks Ashish! I was indeed trying to run DAX in the Query Editor. Simple, silly, rookie mistake. In my defense, I am still learning all the ins and outs of Power BI...

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
PhilipTreacy
Super User
Super User

Hi @cathoms 

Have added the code for fiscal qtr and period which will give you this

fiscal-calendar.png

 

Here's the code, copy paste into a blank query and change your data source.

 

 

let
    Quarters = {3,3,3,4,4,4,1,1,1,2,2,2},
    Period = {7,8,9,10,11,12,1,2,3,4,5,6},
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Fiscal Year", each if Date.Month([Date])>6 then Date.Year([Date])+1 else Date.Year([Date])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Fiscal Quarter", each Quarters{Date.Month([Date])-1}),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Fiscal Period", each Period{Date.Month([Date])-1})
in
    #"Added Custom2"

 

 

Phil


If I answered your question please mark my post as the solution.

If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Thanks Phil. I had a response but it seems to have gone missing after I edited it a couple of times...

 

Anywho, I'm not sure how to change my data source. The excel file I used to create my Date_Lookup table is called "Dates", so I tried the following:

Source = Excel.CurrentWorkbook(){[Name="Dates"]}[Content],

Which returned an error of "We couldn't find an Excel table named 'Dates'."

 

So I tried adding a file path in the parentheses after CurrentWorkbook and got Error: "1 arguments were passed to a function which expects 0."

 

So then I tried using File.Contents("file path") in the parentheses after CurrentWorkbook and got the same error.

 

What am I doing wrong?

 

PhilipTreacy
Super User
Super User

Hi @cathoms 

The line should be

= if Date.Month([Date])>6 then Date.Year([Date])+1 else Date.Year([Date]))

 

The full query looks like this.  It's loading data from a table called Table1

 

 

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Fiscal Year", each if Date.Month([Date])>6 then Date.Year([Date])+1 else Date.Year([Date]))
in
    #"Added Custom"

 

 

Phil 


If I answered your question please mark my post as the solution.

If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


mahoneypat
Microsoft
Microsoft

Please see this post.  It is easier to set up a fiscal calendar table in DAX in my opinion.  This also gives you a full date table you can adapt for your use to save time.

https://www.sqlbi.com/articles/reference-date-table-in-dax-and-power-bi/

 

Regards,

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors