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

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
Super User
Super User

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
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!