Marginal Books

Business Technology Article 05 - The Date Table Template for use in PivotTables, Power QUERY and Power BI

Whether you are using PivotTables, Power PIVOT, Power QUERY or Power BI, at some point, you will need to use a category that is based on a date in your table. Such dates can be: end of month, end of fiscal year, range of years (2018-2019 in case your fiscal year ends in another month than December). 

It is difficult to keep using formulas to create new columns for such fields. This template is a solution.

Here is how it works:

1) You setup the earliest and latest dates in your transactions in the Constants sheet.

2) You indicate which day of the week is your start working day 

3) You indicate which day of the week is your start weekend day

4) Then you generate the rows. The Template will generate as many rows as there are dates between the above two dates.

It will generate around 25 columns. 

5) Copy the generated table to your workbook (or Power BI environment). 

6) Add the new table to the Data Model.

You are now ready to use all these dates:

Year
Day of Month
Day of Year
Weekday Number
Weekday Name
Weekday Name Short
Month Number
Month Name Short
Month Name Long

Quarter
Year Month
Year Quarter
Is Weekend?
Is Weekday?
Year Range
Week Number
ISO Week Numbe

End of Month
End of Month (Day)
Start of Month
Start of Month (Day)
Start of Year (Calendar)
End of Year (Calendar)
Start of Year (Fiscal)
End of Year (Fiscal)

Download the Article


Click Here to download the PDF file containing the full article.

To comment, no need to register or log in with Disqus. Enter any name and a check box will enable you to comment as a guest. Your email will be required but Disqus will not show it in the thread. I am committed to your privacy and confidentiality and will not use it or distribute it. Click Here to review guest privacy and commenting policies in Disqus.