Fiscal Week, Fiscal Month, Fiscal Quarter and Fiscal Year calculations in Microstrategy

Fiscal Week, Fiscal Month, Fiscal Quarter and Fiscal Year calculations in Microstrategy

FiscalWeek

Returns the numeric position of a week within a fiscal year, for a given input date. This function is useful in financial reporting when the start of the fiscal year is different than the start of the calendar year.

Syntax

FiscalWeek<firstWeekDay, firstMonth>(Date/Time)

Where:

•

Date/Time is the input date or timestamp.

•

firstWeekDay (default value is 1) is a parameter that determines which day of the week is considered as the first day of the week. You can type an integer value from 1 to 7, with 1 representing Sunday, 2 representing Monday, and so on until 7 representing Saturday.

•

firstMonth (default value is 1) is a parameter that determines which month is considered as the start of the fiscal year. You can type an integer value from 1 to 12, with 1 representing January, 2 representing February, and so on until 12 representing December.

For information on whether your database supports various date and time functions, see MicroStrategy and Database Support for Functions.

Example

Consider a report or dashboard that includes a Day attribute with a single ID form. You can create a metric with the following definition:

FiscalWeek<firstWeekDay=1, firstMonth=7>(Day)

For each element of the Day attribute, the metric displays the numeric position of the week within the fiscal year for that date. For this example, since the fiscal year starts in July, a date of July 8, 2014 would return 2. This is because the first fiscal week runs from July 1st through July 5th. Then on July 6th, the first Sunday of the fiscal year, the second fiscal week starts. This week includes July 8th, and so 2 is returned.

FiscalMonth

Returns the numeric position of a month within a fiscal year, for a given input date. This function is useful in financial reporting when the start of the fiscal year is different than the start of the calendar year.

Syntax

FiscalMonth<firstMonth>(Date/Time)

Where:

•

Date/Time is the input date or timestamp.

•

firstMonth (default value is 1) is a parameter that determines which month is considered as the start of the fiscal year. You can type an integer value from 1 to 12, with 1 representing January, 2 representing February, and so on until 12 representing December.

For information on whether your database supports various date and time functions, see MicroStrategy and Database Support for Functions.

Example

Consider a report or dashboard that includes a Day attribute with a single ID form. You can create a metric with the following definition:

FiscalMonth<firstMonth=4>(Day)

For each element of the Day attribute, the metric displays the numeric position of the month within the fiscal year for that date. For this example, since the fiscal year starts in April, a date of July 4, 2014 would return 4. This is because July is the fourth month in the fiscal year.

FiscalQuarter

Returns the numeric position of a quarter within a fiscal year, for a given input date. This function is useful in financial reporting when the start of the fiscal year is different than the start of the calendar year.

Syntax

FiscalQuarter<firstMonth>(Date/Time)

Where:

•

Date/Time is the input date or timestamp.

•

firstMonth (default value is 1) is a parameter that determines which month is considered as the start of the fiscal year. You can type an integer value from 1 to 12, with 1 representing January, 2 representing February, and so on until 12 representing December.

For information on whether your database supports various date and time functions, see MicroStrategy and Database Support for Functions.

Example

Consider a report or dashboard that includes a Day attribute with a single ID form. You can create a metric with the following definition:

FiscalQuarter<firstMonth=7>(Day)

For each element of the Day attribute, the metric displays the numeric position of the quarter within the fiscal year for that date. For this example, since the fiscal year starts in July, a date of October 13, 2014 would return 2. This is because October is in the second quarter of the fiscal year.

FiscalYear

Returns the fiscal year of the input date. This function is useful in financial reporting when the start of the fiscal year is different than the start of the calendar year.

When determining the fiscal year, the year returned is the year in which the fiscal year ends. For example, if a fiscal year runs from March 1, 2014 through April 30, 2015, the fiscal year is 2015.

Syntax

FiscalYear<firstMonth>(Date/Time)

Where:

•

Date/Time is the input date or timestamp.

•

firstMonth (default value is 1) is a parameter that determines which month is considered as the start of the fiscal year. You can type an integer value from 1 to 12, with 1 representing January, 2 representing February, and so on until 12 representing December.

For information on whether your database supports various date and time functions, see MicroStrategy and Database Support for Functions.

Example

Consider a report or dashboard that includes a Day attribute with a single ID form. You can create a metric with the following definition:

FiscalYear<firstMonth=2>(Day)

For each element of the Day attribute, the metric displays the fiscal year for that date. A date of July 4, 2013 would have a fiscal year of 2014.