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.
I have a full DimDate dimension with all columns, and I try to filter data for ‘next week’.
I have tried:
'Date'[Week Year Week Number] = CONVERT(CONCATENATE( YEAR(TODAY() ),WEEKNUM(TODAY(),21) ),INTEGER)+1
But of course I have 2 problems:
How can I appropriately filter for ‘next week’ data?
Hi @Anonymous
Check this link:
https://www.vahiddm.com/post/weekly-time-intelligence-dax
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/
@Anonymous you can always create a custom Calendar table with continuous WEEKNUM to help you in next week filtering
Calendar=
VAR _cal1 =
CALENDAR ( DATE ( 2010, 1, 1 ), DATE ( 2020, 1, 1 ) )
VAR _cal2 =
ADDCOLUMNS (
_cal1,
"weekNum",
VAR _minDate = --- what is the min date in this calendar table
MINX ( _cal1, [Date] )
VAR _x =
WEEKDAY ( _minDate, 1 ) - 1
VAR _y = _minDate - _x
VAR _z =
CEILING ( DIVIDE ( ( [Date] - _y ), 7 ), 1 ) ---[Date] - _y gives the last Sunday before the minDate in the calendar
RETURN ---from when the WEEKNUM starts
_z
)
RETURN
_cal2
I had a similar issue to yours, but instead of next week, my desire was the previous month. I needed to get month 12 when it was month 1, not month 0.
I'm still a novice, but I achieved mine with an IF() statement.
Try something like this. The FORMAT() pads the number with a leading zero up to two digits. This also accounts for the year being different when it's the last week of the year.
VAR varNextWeekNum = FORMAT(IF(WEEKNUM(NOW(),21)=52,1,WEEKNUM(NOW(),21)+1),"00")
VAR varNextWeekYear = IF(WEEKNUM(NOW(),21)=52,YEAR(NOW())+1,YEAR(NOW()))
RETURN
CONVERT(CONCATENATE(varNextWeekYear,varNextWeekNum),INTEGER)
EDIT: Fixed code mistake.
There are no built in functions for time intelligence at the week level.
I would start by having a look at: Week-Based Time Intelligence in DAX - SQLBI
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 |