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
cathoms
Helper V
Helper V

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

9 REPLIES 9
amitchandak
Super User
Super User

Hi @amitchandak 

I found your calendar really useful.

I do not have too much experience with power bi. I have tried to add week number to your calendar with no luck.

Is it possible that you add week number so I can sort sales by that, sam I can do with month or quarter?

Thanks in advance.

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
Employee
Employee

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