8. Adding Columns: Power Query Add Column Tab and DAX Calculated Columns
8.10 From Date & Time: Date, Time, Duration
Start with a problem
Shraddha needs Day Name and FY quarter for each order. Quarter of Year returns 2 for April, but the Indian FY Q1 starts in April.
Explain simply
Add Column › From Date & Time gives Date, Time and Duration parts. Calendar functions use January–December, so FY columns (April–March) need a small custom formula.
Show the data
| Order ID | Order Date |
|---|---|
| 301 | 06-Apr-2026 |
| 304 | 04-May-2026 |
| 306 | 05-May-2026 |
About this data: This table uses columns: Order ID, Order Date. This table uses columns: Order ID, Day Name, Calendar Q, FY Quarter, FY. This table uses columns: Menu, Options, Example. This table uses columns: Wrong result, Reason, Correction. Values shown are the lesson examples already on this page (not a new dataset).
Fictional sample data. Practice file: visuals_orders_apr_may_2026.csv (source: src/practice/).
Demonstrate
-
Order Date › Add Column › Date › Day › Name of Day.
Your result should look like this:
Day Name Monday, Monday, Tuesday -
Date › Quarter › Quarter of Year.
Your result should look like this:
Quarter 2, 2, 2 (calendar) -
Custom Column
FY Quarter = Number.IntegerDivide(Number.Mod(Date.Month([Order Date]) - 4 + 12, 12), 3) + 1.Your result should look like this:
FY Quarter 1, 1, 1 -
Custom Column
FY = "FY " & Text.From(if Date.Month([Order Date]) >= 4 then Date.Year([Order Date]) else Date.Year([Order Date]) - 1) & "-" & Text.End(Text.From((if Date.Month([Order Date]) >= 4 then Date.Year([Order Date]) else Date.Year([Order Date]) - 1) + 1), 2).Your result should look like this:
FY FY 2026-27
Show the result
Your result should look like this (fictional sample):
| Order ID | Day Name | Calendar Q | FY Quarter | FY |
|---|---|---|---|---|
| 301 | Monday | 2 | 1 | FY 2026-27 |
| 304 | Monday | 2 | 1 | FY 2026-27 |
| 306 | Tuesday | 2 | 1 | FY 2026-27 |
Explain what happened
Month 4 → (0 mod 12) ÷ 3 + 1 = 1; January → (9) ÷ 3 + 1 = 4. FY starts in April, so April 2026 belongs to FY 2026-27.
Explore further
- Put these columns in the Date table, not the Orders fact (Module 11.5).
Detailed reference for this lesson:
| Menu | Options | Example |
|---|---|---|
| Date | Age, Date Only, Parse, Year (Year, Start/End of Year), Month (Month, Start/End of Month, Days in Month, Name of Month), Quarter (Quarter of Year, Start/End of Quarter), Week (Week of Year, Week of Month, Start/End of Week), Day (Day, Day of Week, Day of Year, Start/End of Day, Name of Day), Subtract Days, Combine Date and Time, Earliest, Latest | Order Date from Order DateTime; Name of Day for weekday analysis; Subtract Days between Order Date and Signup Date (select both) |
| Time | Time Only, Local Time, Parse, Hour (Hour, Start/End of Hour), Minute, Second, Subtract, Combine Date and Time, Earliest, Latest | Order Hour for the "orders by hour" chart (breakfast rush 7–9, evening 7–10 pm) |
| Duration | Days, Hours, Minutes, Seconds, Total Years, Total Days, Total Hours, Total Minutes, Total Seconds, Subtract, Multiply, Divide, Statistics | Delivered − Ordered → Total Minutes = delivery time (Module 7.14) |
Steps in Power BI – Order Date, Hour and Day Name in one go
- Select Order DateTime › Add Column › Date › Date Only → rename Order Date.
- Select Order DateTime › Add Column › Time › Hour › Hour → rename Order Hour.
- Select Order Date › Add Column › Date › Day › Name of Day → Day Name.
- Set the types: Date, Whole Number, Text.
Ravindra Bagale's Tip
Look, friends: adding Year, Month and Day columns to the Orders fact table is a common mistake. They belong in the Date table. The fact table only needs Order Date (and Order Hour if you analyse by hour), which keeps the fact table smaller and your time intelligence consistent. Practise, and it will feel very easy.
Ravindra Bagale's Tip – मराठी
हे बघा मित्रांनो: Orders fact table मध्ये Year, Month आणि Day columns जोडणं ही common चूक आहे. त्यांची जागा Date table मध्ये आहे. Fact table ला फक्त Order Date लागते (आणि तासानुसार analysis करत असाल तर Order Hour), त्यामुळे fact table लहान राहतो आणि time intelligence सुसंगत राहते. Practice करा, मग एकदम सोपं वाटेल.
Ravindra Bagale's Tip – हिंदी
देखो दोस्तों: Orders fact table में Year, Month और Day columns जोड़ना common गलती है. इनकी जगह Date table में है. Fact table को सिर्फ़ Order Date चाहिए (और घंटे के हिसाब से analysis करो तो Order Hour), इससे fact table छोटी रहती है और time intelligence एक जैसी रहती है. Practice करो, फिर बहुत आसान लगेगा.
Practice task
For the Customer table, add Signup Year and Customer Age (days) using Date › Age and Duration › Total Days. Explain why the age changes at every refresh.
Fix common mistakes
| Wrong result | Reason | Correction |
|---|---|---|
| April shown as Q2 | Calendar quarter | Use the FY formula |
| January 2027 labelled FY 2027-28 | Year not shifted | Subtract 1 when month < 4 |
| Day names in another language | Culture setting | Use Date.DayOfWeekName([Order Date], "en-IN") |
Practise independently
Practice task
What FY Quarter and FY label does 15-Jan-2027 get?
Hints
- Month 1.
Solution
FY Quarter 4, FY 2026-27.
Think like a professional
Indian FY handling is a frequent interview check for India-based roles.
Revision card