Excel Web Dashboards

admin

InfoCaptor Enterprise is now available to host it on your windows server or your own PC or laptop.

The benefit of this web based dashboard solution are

  • Works with any Excel file as data source
  • Provides wide range of visualizations
  • Built-in collaboration
  • Works in the browser
  • It is beautiful.

Try the online dashboards or checkout the self hosting dashboard software

Jun
6

Excel Dashboards – Fiscal Quarter, Fiscal Month

admin
Categories: CFO Dashboard, How-To

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.

May
5