0
0
Power BIbi_tool~15 mins

Date table creation in Power BI - Real Business Scenario

Choose your learning style9 modes available
LearnWhyDeepSheetTryChallengeScenarioRecallDash
Scenario Mode
👤 Your Role: You are a data analyst at a retail company.
📋 Request: Your manager wants a comprehensive date table to analyze sales data by different time periods such as year, quarter, month, and day of week.
📊 Data: You have sales data with transaction dates but no date table to support time intelligence calculations.
🎯 Deliverable: Create a date table in Power BI with columns for Date, Year, Quarter, Month, Month Name, Day, and Day of Week.
Progress0 / 9 steps
Sample Data
TransactionIDTransactionDateSalesAmount
10012024-01-15250
10022024-02-20450
10032024-03-05300
10042024-03-25500
10052024-04-10700
10062024-05-15600
10072024-06-30800
10082024-07-04550
10092024-08-18400
10102024-09-22650
1
Step 1: Open Power BI Desktop and go to the Modeling tab.
No formula needed.
Expected Result
Ready to create a new table.
2
Step 2: Create a new table for dates using DAX formula.
DateTable = CALENDAR(DATE(2024,1,1), DATE(2024,12,31))
Expected Result
A table with one column 'Date' containing all dates from January 1, 2024 to December 31, 2024.
3
Step 3: Add a Year column to the DateTable.
Year = YEAR(DateTable[Date])
Expected Result
A new column 'Year' showing the year number for each date.
4
Step 4: Add a Quarter column to the DateTable.
Quarter = "Q" & FORMAT(DateTable[Date], "Q")
Expected Result
A new column 'Quarter' showing values like Q1, Q2, Q3, or Q4.
5
Step 5: Add a Month Number column to the DateTable.
MonthNumber = MONTH(DateTable[Date])
Expected Result
A new column 'MonthNumber' with numbers 1 to 12.
6
Step 6: Add a Month Name column to the DateTable.
MonthName = FORMAT(DateTable[Date], "MMMM")
Expected Result
A new column 'MonthName' with full month names like January, February.
7
Step 7: Add a Day column to the DateTable.
Day = DAY(DateTable[Date])
Expected Result
A new column 'Day' with day numbers 1 to 31.
8
Step 8: Add a Day of Week column to the DateTable.
DayOfWeek = FORMAT(DateTable[Date], "dddd")
Expected Result
A new column 'DayOfWeek' with day names like Monday, Tuesday.
9
Step 9: Mark the DateTable as a Date Table in Power BI.
In Modeling tab, select 'Mark as Date Table' and choose the 'Date' column.
Expected Result
DateTable is recognized as a date table enabling time intelligence functions.
Final Result
DateTable
+------------+------+---------+------------+-----------+-----+-----------+
| Date       | Year | Quarter | MonthNumber| MonthName | Day | DayOfWeek |
+------------+------+---------+------------+-----------+-----+-----------+
| 2024-01-01 | 2024 | Q1      | 1          | January   | 1   | Monday    |
| 2024-01-02 | 2024 | Q1      | 1          | January   | 2   | Tuesday   |
| ...        | ...  | ...     | ...        | ...       | ... | ...       |
| 2024-12-31 | 2024 | Q4      | 12         | December  | 31  | Tuesday   |
+------------+------+---------+------------+-----------+-----+-----------+
✓The date table covers all days in 2024.
✓You can now slice sales data by year, quarter, month, and day of week.
✓Marking the table as a date table enables built-in time intelligence features.
Bonus Challenge

Add columns for Fiscal Year and Fiscal Quarter assuming fiscal year starts in April.

Show Hint
Use DAX functions like IF, MONTH, and YEAR to adjust the year and quarter based on the month.