How to Create Robust Date, Week, Month, Quarter, Year, 15-min & 30-min Columns from a Datetime Field

[manoj_0911]

How to Create Robust Date, Week, Month, Quarter, Year, 15-min & 30-min Columns from a Datetime Field in Power BI

Hi Power BI Community,

I’m working with a transactional table that contains a datetime column called TXN_DATE, where each entry is recorded in 15-minute intervals (e.g., 2025-07-16 09:15:00). I want to use this field to support different levels of time-based reporting in Power BI — without any sorting or grouping issues.

---

✅ Goal:

From TXN_DATE, I want to create the following derived fields in Power BI (DAX) or Power Query in a production-ready way:

1. Date only – 2025-07-16

2. Month – July 2025 or 2025-07

3. Week – ISO week (or Power BI default week, if ISO is not possible)

4. Quarter – Q3 2025 or 2025-Q3

5. Year – 2025

6. 15-Min Interval – 2025-07-16 09:15

7. 30-Min Interval – 2025-07-16 09:00 or 09:30 etc.

8. ✅ All of them must sort correctly and support date slicers and grouping

---

🔍 Constraints / Notes:

• The TXN_DATE column is a datetime column in SQL and in Power BI, not a string.

• I want to avoid sorting issues that occur with text-based fields like "July" or "Q1".

• I want this solution to be usable for daily, weekly, monthly, quarterly level reporting.

Thank you!

Looking forward to your guidance on a clean, efficient, and scalable solution. 

[v-priyankata · Accepted solution]

Hi @manoj_0911 

Thank you for reaching out to the Microsoft Fabric Forum Community.@dd333 Thanks for the inputs, those are very helpful.
Please try those points, like below
1. create a date table by using below DAX and make a relationship between your table and date table. 

DateTable = 
VAR StartDate = DATE(2015, 1, 1)
VAR EndDate = DATE(2030, 12, 31)
RETURN
ADDCOLUMNS (
CALENDAR (StartDate, EndDate),
"Year", YEAR([Date]),
"Month Number", MONTH([Date]),
"Month Name", FORMAT([Date], "MMMM"),
"Month Short", FORMAT([Date], "MMM"),
"Year-Month", FORMAT([Date], "YYYY-MM"),
"YearMonthSort", YEAR([Date]) * 100 + MONTH([Date]),
"Quarter", "Q" & FORMAT([Date], "Q"),
"Year-Quarter", FORMAT([Date], "YYYY") & "-Q" & FORMAT([Date], "Q"),
"Quarter Sort", YEAR([Date]) * 10 + QUARTER([Date]),
"ISO Week Number", WEEKNUM([Date], 21),
"ISO Year", YEAR([Date] - WEEKDAY([Date], 2) + 4),
"ISO Year-Week", FORMAT(YEAR([Date] - WEEKDAY([Date], 2) + 4), "0000") & "-W" & FORMAT(WEEKNUM([Date], 21), "00"),
"Day Name", FORMAT([Date], "dddd"),
"Day Number of Week", WEEKDAY([Date], 2),
"Start of Month", DATE(YEAR([Date]), MONTH([Date]), 1),
"End of Month", EOMONTH([Date], 0),
"Start of Week", [Date] - WEEKDAY([Date], 2) + 1,
"End of Week", [Date] + (7 - WEEKDAY([Date], 2)),
"Fiscal Year", IF(MONTH([Date]) >= 4, YEAR([Date]), YEAR([Date]) - 1),
"Fiscal Quarter", 
SWITCH(
TRUE(),
MONTH([Date]) IN {4,5,6}, "Q1",
MONTH([Date]) IN {7,8,9}, "Q2",
MONTH([Date]) IN {10,11,12}, "Q3",
MONTH([Date]) IN {1,2,3}, "Q4"
),
"Fiscal Year-Quarter", 
VAR fy = IF(MONTH([Date]) >= 4, YEAR([Date]), YEAR([Date]) - 1)
VAR fq = SWITCH(
TRUE(),
MONTH([Date]) IN {4,5,6}, "Q1",
MONTH([Date]) IN {7,8,9}, "Q2",
MONTH([Date]) IN {10,11,12}, "Q3",
MONTH([Date]) IN {1,2,3}, "Q4"
)
RETURN FORMAT(fy, "0000") & "-" & fq
)

 
2. create 2 columns (your table) and then use them in visual as per your requirement.

TXN_15Min =
VAR dt = 'Transactions'[TXN_DATE]
VAR Minutes = MINUTE(dt)
VAR RoundedMinutes = FLOOR(Minutes, 15)
RETURN 
DATETIME(YEAR(dt), MONTH(dt), DAY(dt), HOUR(dt), RoundedMinutes, 0)

TXN_30Min =
VAR dt = 'Transactions'[TXN_DATE]
VAR Minutes = MINUTE(dt)
VAR RoundedMinutes = FLOOR(Minutes, 30)
RETURN 
DATETIME(YEAR(dt), MONTH(dt), DAY(dt), HOUR(dt), RoundedMinutes, 0)

 
If you encounter any issues, please contact the community for further assistance.
Thanks

[v-priyankata]

Hi @manoj_0911 

I hope the information provided was helpful. If you still have questions, please don't hesitate to reach out to the community.

[v-priyankata]

Hi @manoj_0911 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

[v-priyankata]

Hi @manoj_0911 

Hope everything’s going smoothly on your end. We haven’t heard back from you, so I wanted to check if the issue got sorted. if you have any other issues please reach community.

[dd333]

Hi, 

You're better off building a separate table for that, you can extend it out for several years to create a rolling table that you can reference by day / month / quarter, etc. 

Good reference - 

Creating a date dimension or calendar table in SQL Server