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

[cathoms]

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.

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!

[amitchandak]

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

[DavidAguilo]

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 · Accepted solution]

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.

[cathoms]

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

[Ashish_Mathur]

You are welcome.

[PhilipTreacy]

Hi @cathoms 

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

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.

[cathoms]

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]

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.

[mahoneypat]

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