cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

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

Accepted Solutions
Highlighted
Super User III
Super User III

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

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? Please mark my post as a solution! Kudos are also appreciated!

Proud to be a Super User!




Highlighted
Resident Rockstar
Resident Rockstar

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.

Highlighted
Resident Rockstar
Resident Rockstar

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.

Highlighted
Super User III
Super User III

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

Highlighted
Super User IV
Super User IV

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted

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

Highlighted

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?

 

Highlighted

You are welcome.


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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors