Dashboard Mode - Date table creation
Business Question
How can we create a complete date table in Power BI to analyze sales data by year, quarter, month, and day?
0
0
Date table creation in Power BI - Dashboard Guide
Sample Data
| Date | Sales Amount |
|---|---|
| 2024-01-15 | 100 |
| 2024-02-20 | 150 |
| 2024-03-10 | 200 |
| 2024-04-05 | 250 |
| 2024-05-25 | 300 |
| 2024-06-30 | 350 |
| 2024-07-15 | 400 |
Dashboard Components
- Date Table (Calculated Table):
DateTable = CALENDAR(DATE(2024,1,1), DATE(2024,12,31))
Creates a date table for the full year 2024. - Date Table with Columns: Add columns for Year, Quarter, Month, and Day:
Year = YEAR(DateTable[Date])Quarter = "Q" & FORMAT(DateTable[Date], "Q")Month = FORMAT(DateTable[Date], "MMMM")Day = DAY(DateTable[Date]) - Total Sales (Measure):
Total Sales = SUM('Sales'[Sales Amount])
Calculates total sales amount. - Sales by Month (Bar Chart): Visual showing total sales per month using DateTable[Month] on axis and Total Sales measure as values.
- Sales by Quarter (Card): Shows total sales for the selected quarter.
Dashboard Layout
+----------------------+----------------------+ | Sales by Month | Sales by Quarter | | (Bar Chart) | (Card) | +----------------------+----------------------+ | Total Sales (KPI) | +------------------------------------------------+ | (Date Table used in filters) | +------------------------------------------------+
Interactivity
The Date Table is connected to the Sales data by the Date column. Filters on Year, Quarter, or Month from the Date Table update the Sales by Month chart, Sales by Quarter card, and Total Sales KPI dynamically.
Self Check
If you add a filter to select only Quarter = "Q2", which components update and what do they show?
- Sales by Month chart updates to show sales only for April, May, and June.
- Sales by Quarter card shows total sales for Q2.
- Total Sales KPI shows total sales for Q2.
Key Result
A Power BI dashboard showing total sales with a date table enabling analysis by year, quarter, and month.