Dates

Full details on the Dates available in Microsoft Power BI from Xero with Connectorly.

Nick Maxwell avatar
Written by Nick Maxwell
Updated over a week ago

Overview

Financial reporting makes little sense without the dimension of time being applied. The Dates table provides an easy way to group and/or label time.

Bank Transactions, Bank Transfers, Budgets, Invoices and Journals are related to the Date field so that they can all be filtered by the same period at the same time.

The Different Calendars

The fields in the Dates table are grouped into four different calendars.

Below, for clarity, we've presented each calendar with its own Data Dictionary. In reality, all fields, including the Date field are in a single Dates table.

Calendar

In the 'normal' Gregorian Calendar; years start on January 1st and finish on December 31st.

Calendar Data Dictionary

Field

Type

Notes

Calendar Half End

Date

The last day of the half year.

Calendar Half Name

Text

The name of the half year; e.g. 2023 H1

Calendar Half Short Name

Text

The short name.

e.g. H1

Calendar Half Start

Date

The first day of the half year.

Calendar Month End

Date

The last day of the month.

Calendar Month Name

Text

e.g. February 2023

Calendar Month Short Name

Text

e.g. February

Calendar Month Start

Date

The first day of the month.

Calendar Quarter End

Date

The last day of the quarter.

Calendar Quarter Name

Text

e.g. 2023 Q2

Calendar Quarter Short Name

Text

e.g. Q2

Calendar Quarter Start

Date

The first day of the quarter.

Calendar Year as Number

Number

2023

Calendar Year End

Date

The last day of the year; e.g. 31 December 2023

Calendar Year Name

Text

2023

Calendar Year Start

Date

The first day of the year;

e.g. 1 January 2023

Fiscal calendar

The Fiscal calendar is the most commonly used calendar used for financial reporting and uses the fiscal year from your Xero organisation. If the case of multiple grouped Xero organisations, the primary Organisation's fiscal year is used.

Fiscal calendar Data Dictionary

Field

Type

Notes

Fiscal Half

Text

FY H2

Fiscal Half End

Date

The last day of the fiscal half year.

e.g. 31 March 2023

Fiscal Half Name

Text

e.g. FY 2021-2022 H2

Fiscal Half Short Name

Text

e.g. FY21 H2

Fiscal Half Start

Date

The first day of the fiscal half year.

e.g 1 April 2023

Fiscal Month End

Date

The last day of the month.

Fiscal Month Name

Text

e.g. April 2023

Fiscal Month Short Name

Text

e.g. April

Fiscal Month Start

Date

The first day of the month.

Fiscal Quarter

Text

FY Q4

Fiscal Quarter End

Date

The last day of the quarter.

Fiscal Quarter Name

Text

FY 2021-2022 Q4

Fiscal Quarter Short Name

Text

FY21 Q4

Fiscal Quarter Start

Date

The first day of the quarter.

Fiscal Year End

Date

The last day of the fiscal year.

e.g. 30 September 2023

Fiscal Year Long Name

Text

FY 2022-2023

Fiscal Year Name

Text

FY 2022

Fiscal Year Short Name

Text

FY22

Fiscal Year Start

Date

The first day of the fiscal year.

e.g. 1 September 2022

ISO Year Calendar

The ISO Year calendar splits months into full weeks, following a 4-4-5 week pattern, so 'January' has four weeks (28 days), 'February' has four weeks and 'March' has five (35 days).

Weeks (and hence Months and Years) always start on a Monday and end on a Sunday.

Years usually have 364 days (52 weeks). In order to catch up with reality(!) every now and then an extra week is added, in which case January has 5 weeks.

Why use the ISO Year Calendar?

This calendar is useful if you want to closely track weekly figures and is often used in retail.

Disadvantages of the ISO Year Calendar

Its disadvantages are that you can't compare months, as they are not the same period. Mind you, that's also true (and less pronounced so overlooked) for Fiscal and Gregorian calendar comparisons!


ISO Year Data Dictionary

All examples below are given for the ISO Year 2022.

Field

Type

Notes

ISO Year as Number

Number

e.g. 2022

ISO Year End

Date

e.g. 1 January 2023 (which was a Sunday)

ISO Year Half End

Date

e.g. 3 July 2022

ISO Year Half Name

Text

e.g. 2022 H1

ISO Year Half Short Name

Text

H1

ISO Year Half Start

Date

e.g. 4 July 2022

ISO Year Month End

Date

29 May 2022

ISO Year Month Name

Text

May 2022

Be careful. ISO Year May 2022 had only 28 days, started on 2 May and finished on 29 May.

ISO Year Month Short Name

Text

May

ISO Year Month Start

Date

2 May 2022

ISO Year Name

Text

2022

ISO Year Quarter End

Date

3 April 2022

ISO Year Quarter Name

Text

2022 Q1

ISO Year Quarter Short Name

Text

Q1

ISO Year Quarter Start

Date

3 January 2022

ISO Year Start

Date

e.g. 3 January 2022 (which was a Monday)

ISO Year Week

Text

Week 8

ISO Year Week End

Date

27 February 2022

ISO Year Week No

Number

8

ISO Year Week Start

Date

21 February 2022

ISO Fiscal Year Calendar

The ISO Fiscal Year calendar uses the ISO Year calendar, except that the year starts on the closest Monday to the beginning of the fiscal year as set in Xero (or in the case of grouped Xero's, the primary Organisation's fiscal year).

The advantages and disadvantages are as per the ISO Year Calendar, with the additional advantage that it can start at a point other than the nearest Monday to January 1.

ISO Fiscal Year Data Dictionary

All examples below are given for a Fiscal ISO Year 2022 that's Fiscal year runs from March and ends in February.

In 2022, the first Monday of March was 7 March.

Field

Type

Notes

ISO Fiscal Year End

Date

5 March 2023

ISO Fiscal Year Half

Text

FY H1

ISO Fiscal Year Half End

Date

4 September 2022

ISO Fiscal Year Half Name

Text

FY 2022-2023 H1

ISO Fiscal Year Half Short Name

Text

FY22 H1

ISO Fiscal Year Half Start

Date

7 March 2022

ISO Fiscal Year Long Name

Text

FY 2022-2023

ISO Fiscal Year Month End

Date

1 May 2022

ISO Fiscal Year Month Name

Text

Month 2 2022

ISO Fiscal Year Month Short Name

Text

Month 2

ISO Fiscal Year Month Start

Date

4 April 2022

ISO Fiscal Year Name

Text

FY 2022

ISO Fiscal Year Quarter

Text

FY Q2

ISO Fiscal Year Quarter End

Date

4 September 2022

ISO Fiscal Year Quarter Name

Text

FY 2022-2023 Q2

ISO Fiscal Year Quarter Short Name

Text

FY Q2

ISO Fiscal Year Quarter Start

Date

6 June 2022

ISO Fiscal Year Short Name

Text

FY22

ISO Fiscal Year Start

Date

7 March 2022

ISO Fiscal Year Week

Text

Week 19

ISO Fiscal Year Week End

Date

17 July 2022

ISO Fiscal Year Week No

Number

19

ISO Fiscal Year Week Start

Date

11 July 2022


​

Did this answer your question?