Articles Comments

Free Excel Dashboards » Dashboard, Excel Dashboard, Excel Dashboard Software » Excel Dashboards – Fiscal Quarter, Fiscal Month

Excel Dashboards – Fiscal Quarter, Fiscal Month

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

Image

 

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”
  • Image
  • 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

Image

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

No related posts.

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

Filed under: Dashboard, Excel Dashboard, Excel Dashboard Software

Comments are closed.