Business Technology Article 05 - The Date Table Template for use in PivotTables, Power QUERY and Power BI
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:
Day of Month
Day of Year
Weekday Name Short
Month Name Short
Month Name Long
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)
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.