Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi Community,
Requirement:
Im actually stuck in calculating Weeknumber & QuarterNumber. Based on the min & max date in the Aria(Table) date column it should consider the week 1 start from min date & when next year comes up it should continue rather than re-start from 1st week? E.g. 25 july is minmum date then 1 week & 3 jan 2024 is max date then 23rd week.
1. Below is the Calculated date table. The WeekNumber should not restart from Jan-01-2024 instead it should continue from the last calculation as week-23. How?
2. How to calculate QuarterNumber by considering the same min & max date method ? The quarter should start with Q1 instead of Q3 for minimum date & so on calculate till max date.
Pbix File Link
Time_Period =
VAR _StartDate =
MIN ( 'ARIA'[EVENT_DAY] )
VAR _EndDate =
MAX ( 'ARIA'[EVENT_DAY] )
VAR _WeekStart =
WEEKNUM ( _StartDate )
RETURN
ADDCOLUMNS (
CALENDAR ( _StartDate, _EndDate ),
"Year", YEAR ( [Date] ),
"Month", FORMAT ( [Date], "mmmm" ),
"MonthNum", MONTH ( [Date] ),
"Quarter", ( "Q" & FORMAT ( [Date], "Q" ) ),
"DayOfWeekName", FORMAT ( [Date], "dddd" ),
"WeekNumber",
IF (
[Date] >= DATE ( YEAR ( [Date] ), 8, 1 ),
QUOTIENT ( [Date] - DATE ( YEAR ( [Date] ), 8, 1 ), 7 ) + 2,
1
)
)
Thanks in advance!
Consider using an external Calendar table instead, for example an Excel file on a SharePoint.
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
23 | |
7 | |
7 | |
6 | |
6 |
User | Count |
---|---|
27 | |
12 | |
10 | |
9 | |
6 |