0
0
Power BIbi_tool~15 mins

Mark as date table 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 you to create accurate time-based reports using a Date table. They ask you to mark the Date table properly in Power BI so that time intelligence functions work correctly.
📊 Data: You have a Date table with columns: Date, Year, Month, Quarter, and Day. You also have a Sales table with sales transactions including a Date column.
🎯 Deliverable: You need to mark the Date table as a Date table in Power BI and create a simple report showing total sales by month.
Progress0 / 5 steps
Sample Data
DateYearMonthQuarterDay
2024-01-012024JanuaryQ11
2024-01-022024JanuaryQ12
2024-02-012024FebruaryQ11
2024-02-152024FebruaryQ115
2024-03-012024MarchQ11
2024-03-312024MarchQ131
2024-04-012024AprilQ21
2024-04-152024AprilQ215
2024-05-012024MayQ21
2024-05-202024MayQ220
2024-06-012024JuneQ21
2024-06-302024JuneQ230
1
Step 1: Load the Date table and Sales table into Power BI Desktop.
Use 'Get Data' to import both tables from your data source.
Expected Result
Both tables appear in the Fields pane.
2
Step 2: Create a relationship between the Sales table and the Date table using the Date column.
In the Model view, drag the Date column from Sales to the Date column in Date table.
Expected Result
A one-to-many relationship is created from Date table to Sales table.
3
Step 3: Mark the Date table as a Date table in Power BI.
Select the Date table, go to Table tools ribbon, click 'Mark as Date Table', then select the Date column as the unique date identifier.
Expected Result
Date table is marked as a Date table with the Date column selected.
4
Step 4: Create a measure to calculate total sales.
Total Sales = SUM(Sales[Amount])
Expected Result
Measure 'Total Sales' is created and sums the Amount column in Sales.
5
Step 5: Build a report visual showing total sales by month.
Add a clustered column chart. Set Axis to Date[Month], Values to Total Sales measure.
Expected Result
Chart displays total sales for each month.
Final Result
Total Sales by Month

January   | ████████  15000
February  | ██████    12000
March     | ███████   14000
April     | ████      8000
May       | █████     10000
June      | ███       6000
✓Sales peak in January and March.
✓April and June have lower sales.
✓Marking the Date table enables correct time intelligence calculations.
Bonus Challenge

Create a measure to calculate Year-to-Date (YTD) sales using the marked Date table.

Show Hint
Use the DAX function TOTALYTD with the Date column from the marked Date table.