Free Excel Dashboards

When you use the Add Date hierarchy columns for any date, by default it brings all calendar quarter and calendar month.

 

How do you get Fiscal Quarter

Let say your fiscal year begins from 1st July then

First Quarter = { 7 , 8 , 9}

Second Quarter = { 10, 11, 12}

Third Quarter = {1, 2, 3}

Fourth Quarter = { 4, 5, 6 }

So with the above logic let us create a formula

• Right click on the date column
• Select “Create Calculation”
• 
• Type the following formula
• switch(
  month(”Order Date”) in (7,8,9) , ‘Qtr1′ ,
  month(”Order Date”) in (10,11,12) , ‘Qtr2′ ,
  month(”Order Date”) in (1,2,3) , ‘Qtr3′ ,
  month(”Order Date”) in (4,5,6) , ‘Qtr4′
  )
• Click on ‘Use This Formula’

Here is the Fiscal Quarter

Note: You can change the Quarter text to anything like ‘Q1′ instead of ‘Qtr1′

Fiscal Month

Similarly, if you need fiscal month, we can use the following formula

switch(
month(”Order Date”) >= 7 , month(”Order Date”)-6 ,
month(”Order Date”) < 7 , month(”Order Date”) + 6
)

Fiscal Year

switch(
month(”Order Date”) >= 7 , year(”Order Date”) ,
month(”Order Date”) < 7 , year(”Order Date”) – 1
)

 

If you have a different Fiscal year begining then replace the numbers accordingly.

For more information visit Excel Dashboards – Fiscal Quarter, Fiscal Month

Related posts:

1. Excel Dashboards – Fiscal Quarter, Fiscal Month When you use the Add Date hierarchy columns for any...
2. How to build Excel Dashboards – 5 Previous Article – Create Excel Dashboards – 4 Add Date...
3. Excel Date Functions In the information jungle for Excel, when it comes to...
4. How to build Excel Dashboards – 7 Previous – Excel Dashboard Tutorial – 6 Create Bar Chart...
5. How to build a Performance Dashboard? Traditional report layouts made it difficult for managers to quickly...

Related posts brought to you by Yet Another Related Posts Plugin.