The request
From Chris Martinez, Marketing Planner
“I'm planning next year's campaign calendar. Which months of the year are busiest for us, taking all three years together? I don't need each year separately, just January through December, with the number of orders in each.”
Your goal
Add a calculated column called Order Month to orders that holds the month number (1 to 12) of orders[order_date]. The matrix counts the given measure Orders by your column.
- Add the column
Order Monthtoordersnot done yet - The Total row right toonot done yet
Your calculated column
- Visual
- A matrix: rows = your new column on orders, values = [Orders]
- Measures in the report
Orders = COUNTROWS(orders)
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…
Reference solution
| order_id | customer_id | order_date | status | channel | shipping_fee |
|---|---|---|---|---|---|
| 1,001 | 125 | 2023-01-01 | completed | web | 0 |
| 1,002 | 156 | 2023-01-02 | completed | web | 0 |
| 1,003 | 22 | 2023-01-02 | completed | marketplace | 4.99 |
| 1,004 | 48 | 2023-01-02 | completed | marketplace | 4.99 |
| 1,005 | 82 | 2023-01-02 | completed | web | 0 |
| 1,006 | 157 | 2023-01-02 | cancelled | marketplace | 4.99 |
| 1,007 | 21 | 2023-01-02 | completed | marketplace | 7.65 |
| 1,008 | 157 | 2023-01-02 | completed | web | 0 |
Mote checks your result
Press Check. Mote fills in your answer and compares it with what the report needs.
What Chris gets, and why
The lesson
Order dates are too detailed for Chris’s question: three years of days, up to 1,096 of them. He needs them folded into twelve months, each one counting every year’s orders.
What you are working in
This page is a practice report built for this site, since Power BI Desktop runs only on
Windows. Your column is added to orders, and the matrix groups the Orders measure
(COUNTROWS(orders)) by it. The engine checks each row and the total.
In Power BI Desktop: select orders in the Data pane, then Table tools → New column,
type the formula and press Enter. Switch to Table view to see the new column beside the
dates.
Date functions in DAX
DAX has the same date functions you know from Excel, and they work on a date column one row at a time:
| Function | Returns | For 2025-03-14 |
|---|---|---|
YEAR(date) |
the year | 2025 |
MONTH(date) |
the month number | 3 |
DAY(date) |
the day of the month | 14 |
QUARTER(date) |
the quarter number | 1 |
WEEKDAY(date, 2) |
the weekday, Monday = 1 | 5 |
Order Month = MONTH(orders[order_date])
Order Year = YEAR(orders[order_date])Each order row gets one number. Put Order Month on the matrix rows and COUNTROWS(orders) is
evaluated once per month number, across all years together.
Month or year?
The two columns answer different questions. Order Year gives three rows, one per year: is the
business growing? Order Month gives twelve: which part of the year is busy? Chris asked the
second question, so the column needs the month only. Combine both, Year then Month, when you want
36 separate months in order.
Numbers, not names, for sorting
A month name looks friendlier, but text sorts alphabetically: Apr, Aug, Dec, Feb… A number sorts in calendar order. When you do want names on a visual, keep the number too and use Column tools → Sort by column to sort the name by the number.
A note on the Date table
In the full model, the marked Date table already has Year, Quarter, Month and Month Number,
and they filter every fact table at once. That is where date columns usually belong. A column
like this one on a fact table is still common for a quick answer, an export, or a model without
a calendar, and it is the cleanest way to learn how date functions behave row by row.
Common mistakes
YEARinstead ofMONTH. Three rows instead of twelve.FORMAT(orders[order_date], "MMM"). Gives names, sorted alphabetically.- A measure.
MONTH()needs one date. In a measure, the column holds thousands of dates and there is no current row to read.
Lantern lit!
Practised: calculated columnmonthyeardate functionsseasonality
- Solved
- No hints
- First run