Computer Tutoring Logo
Est. 2002
Menu Button

PowerBI Date Table

Why do you need a Date Table?

OK! You may be wondering why you would need a Date Table in Power BI. 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 Date Table Power BI 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.
    Power BI modeling tab

  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.
    Data view power bi
  6. Click New Column at the top. (You'll find it under the Modeling tab).
    New column button power bi
  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.
    Month Column in Power BI
  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")
  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)
    Sort By Column in PowerBI
  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.
    Relationship button power bi
  15. In the Relationships view drag from Date in the DimDate table to Date Sold in the factSales table.
    Creating a relationship using a date 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.
    relationship cardinality in power bi
  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).
    Mark as Date Table in Power BI
  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.
    Power BI drill down icon
  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:
    Matrix fiscal year power bi
  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.

    Show items with no data power bi

There you go I hope that you were able to follow all the steps, I also hope that I've made no mistakes. BTW, here is the completed 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 do with this. 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 tutorial.