9. Lighthouse

Level 1 · A weekend column on the Date table

From Tyler Reed, Customer Service Manager

“I staff the support desk lighter on Saturdays and Sundays because I assumed weekends are quiet. Before I sign next year's rota, I want to see completed sales on weekend days against weekdays. If weekends are busier per day, I'm moving people.”

Your goal

Add a calculated column called Day Type to the 'Date' table: "Weekend" when 'Date'[Weekday Number] is 6 or 7 (Saturday, Sunday), "Weekday" otherwise. The matrix then groups the given Sales measure by your column.

  • Add the column Day Type to Date
  • The Total row right too

Your calculated column

DAX
Visual
A matrix: rows = your new column on Date, values = [Sales]
Measures in the report
Sales = CALCULATE(SUM(order_items[line_total]), orders[status] = "completed")
Data pane: tables and columns
  • order_items order_item_id, order_id, product_id, quantity, unit_price, discount_pct, line_total
  • orders order_id, customer_id, order_date, status, channel, shipping_fee, coupon_code
  • products product_id, name, category, subcategory, unit_price, unit_cost, launched_on, is_active
  • customers customer_id, first_name, last_name, email, city, state, signup_date, segment, referral_source
  • returns return_id, order_item_id, returned_on, reason, refund_amount
  • Date Date, Year, Quarter, Month Number, Month, Year Month, Year Quarter, Day, Weekday Number, Weekday

Loading the editor…

    VisualData view of Date: your column appears in the visual once you press Check
    DateYearQuarterMonth NumberMonthYear Month
    2023-01-012,023Q11Jan2023-01
    2023-01-022,023Q11Jan2023-01
    2023-01-032,023Q11Jan2023-01
    2023-01-042,023Q11Jan2023-01
    2023-01-052,023Q11Jan2023-01
    2023-01-062,023Q11Jan2023-01
    2023-01-072,023Q11Jan2023-01
    2023-01-082,023Q11Jan2023-01

    Mote checks your result

    Press Check. Mote fills in your answer and compares it with what the report needs.

    The lesson

    Time intelligence in Power BI starts with a table, not a function. Every year-to-date, last-year and month-over-month measure in this chapter leans on the 'Date' table, so it is worth knowing what a good one looks like before you use it.

    What you are working in

    This page runs a practice DAX engine built for this site. It evaluates your calculated column on every day of the 'Date' table, then groups Sales by the new column in the matrix. In Power BI Desktop: select the Date table in Data view, choose Table tools → New column, type the formula and press Enter. Then drag the column into a matrix’s Rows.

    What makes a Date table

    The model’s 'Date' table has one row per day from 2023-01-01 to 2025-12-31, with columns such as Year, Quarter, Month Number, Month, Year Month (“2025-01”), Weekday Number (1 = Monday) and Weekday (“Mon”). Three rules make it work:

    1. Continuous. Every day from the first to the last, even days with no orders. Time intelligence functions shift and extend date ranges; a missing day breaks “same period last year” and running totals.
    2. Related. orders[order_date] is related many-to-one to 'Date'[Date]. Filters on the calendar reach the orders, and through them the order lines. The date column holds dates without a time, so they match.
    3. Marked. Table tools → Mark as date table, then pick Date. Power BI checks the column has unique, continuous dates and then knows which table to use for time intelligence. The seventh level of this chapter shows the symptom when that step is skipped.

    In Power BI you usually build the table with DAX, Modeling → New table, using CALENDAR(DATE(2023,1,1), DATE(2025,12,31)) and ADDCOLUMNS for Year, Month and the rest, or in Power Query. This practice engine doesn’t create tables, so the calendar is given.

    Adding a label column

    Day Type = IF('Date'[Weekday Number] >= 6, "Weekend", "Weekday")

    The column runs once per day. On 2025-11-29, a Saturday, Weekday Number is 6 and the column says Weekend. Because the label lives on the calendar, it works for every fact related to it: orders, returns, web traffic. Other useful calendar columns follow the same pattern: a fiscal year, a season, a holiday flag, an “is past” flag for hiding future months.

    Why the Date table and not orders

    You could add the same column to orders. But the next fact table would need its own copy, and slicers on two different “Day Type” columns don’t talk to each other. One calendar, related to every fact, gives one slicer that filters them all.

    Common mistakes

    • Date without quotes. DATE is also a function name, so Power BI always writes the table as 'Date'. Keep the single quotes.
    • WEEKDAY’s default. WEEKDAY('Date'[Date]) numbers Sunday as 1. Use WEEKDAY(…, 2) for Monday = 1, or the ready-made Weekday Number column.
    • Sorting text labels. “Mon”, “Tue” sort alphabetically unless you set Column tools → Sort by column to a number column. The Month column here is already sorted by Month Number.

    Cookie settings

    Pick what may run on this site. You can change this any time from "Cookie settings" at the bottom of every page.