Creating a Date Table in Power BI

Why Do You Need a Power BI Date Table?

OK! So why do you need a Power BI Date Table? Especially when you consider that you already have a column of dates in your main table (Fact Table). It used to be the case that you had to use a date table if you wanted to take full advantage of all the fancy date functions that come with DAX.

1. Open the Create Power BI Date Table exercise file.
   This file has one table called factSales, this table has a column called date. The first thing that we need to do is establish the first day in the year of the first transaction, or sale. At the same time we need to find the last day in the last transaction or sale. Then we have the date basis for our table.
   
2. In Power BI click Modeling then click New Table.
   
   
   
3. In the formula bar at the top type the following:
   DimDate = CALENDAR(
   DATE(YEAR(MIN(factSales[Date Sold])),1,1),
   DATE(YEAR(MAX(factSales[Date Sold])),12,31)
   )
   To create a new line you can hold down Shift & Enter.
4. When finished click on the tick or press enter and you'll have a wonderful new date table.
5. Click on DimDate to the right then click on the Data view button.
   
   
6. Click New Column at the top. (You'll find it under the Modeling tab).
   
7. Type the following formula to extract the month:
   Month = FORMAT(DimDate[Date],"mmmm")
   Note the new column with the date formatted as the full month name.
   
   
8. Use the following formulas to create the necessary columns:
   • MonthNo = MONTH(DimDate[Date])
   • Year = YEAR(DimDate[Date])
   • Quarter = FORMAT(DimDate[Date],"Q")
   • Fiscal Year = IF(
     MONTH(DimDate[Date])>=4,
     YEAR(DimDate[Date]) & "/" & YEAR(DimDate[Date])+1,
     YEAR(DimDate[Date])-1 & "/" & YEAR(DimDate[Date]))
   • Fiscal Month No = SWITCH(MONTH(DimDate[Date]),
     4,1,
     5,2,
     6,3,
     7,4,
     8,5,
     9,6,
     10,7,
     11,8,
     12,9,
     1,10,
     2,11,
     12
     )
   • Fiscal Month = FORMAT(DimDate[Date],"mmmm")
• Alternatively, you can use this formula: Fiscal Month No = MOD(MONTH([Date Sold]) - 4, 12) + 1
• MONTH(DimDate[Date]): Extracts the calendar month as a number (1 for January, 2 for February, etc.).
• - 4: Adjusts the month number to align with the fiscal year starting in April. For example:
  • April becomes 0
  • May becomes 1
  • March (of the following year) becomes -1
• MOD(..., 12): Ensures the numbers wrap around correctly for months like January, February, and March that would otherwise result in negative numbers.
• + 1: Shifts the result so that fiscal months are 1-based (rather than starting at 0).
9. Great now we're almost there. What we need to do is to sort the Month by Month number and Fiscal Month by Fiscal Month No.
10. Click on the Month column.
11. Click on Sort By. (You'll find it under the Modeling tab)
    
12. Choose MonthNo
13. Now sort Fiscal Month by Fiscal Month No
    If you don't sort the month or the fiscal month by the relevant month nubers Power BI will sort the column alphabetically when you add them to the page.
14. Now to form the relationships, click on the relationships button on the left.
    
15. In the Relationships view drag from Date in the DimDate table to Date Sold in the factSales table.
    
16. Note that in the relationship above, the number 1 is at the beginning of each relationship. We need to adjust this so the relation is a one to many relationship. That is one one the DimDate side to many on the factSales side.
17. Double click on the relationship line.
    
18. Ensure the top table is DimDate and the lower table is factSales.
19. From the Cardinality drop down list choose One to many (1:*) then click OK.
20. Finally there is one more step, we need to set DimDate as a date table.
21. Click on the Data button on the left.
22. On the right click on DimDate
23. Click on Mark as Date Table then Mark as Date Table from the drop down list. (Modeling tab - top of screen).
    
24. In the Mark as date table box choose Date from the Date Column drop down list. Then Click OK.
25. Now your Date Table is ready to use. Just to prove how handy your newly formed date table is you can try the following:
26. Back on Reports create a matrix and add
    • Fiscal Year & Fiscal Month to Rows (DimDate)
    • Total to Values (factSales)
27. Drill down by using the little fork looking icon located in the bottom right of the matrix visualisation.
    
28. With Total selected use the formatting options to display the value as currency. Use the paint roller to change the formatting and you should have a matrix visualation that looks like this:
    
29. To the get the January to appear at the top I clicked the drop down list under Fiscal month in rows and choose show items with no data.
    
    

There you go I hope that you were able to follow all the steps and you have created your first Power BI Date Table. I also hope that I've made no mistakes. Also, to help you along, here is the completed Power BI Date Table file should you want to see what it looks like.

Hope the above helps, of course there's so much more you can add to a Power BI Date Table. If you haven't already done so check out my Power BI tutorials videos. If you want to know more about sorting by month, weekday or even season then check out the sort by month step by step Power BI tutorial.