Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

is there a nextweek function or similar in dax?

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:

  •  when week is 52, I will just get 53.
  • Also the column  Week Year Week Number   has values like 202202 And my WEEKNUM concat returns something like 20222 (they are different!)

 

How can I appropriately filter for ‘next week’ data?

 

4 REPLIES 4
VahidDM
Super User
Super User

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/

 

 

smpa01
Super User
Super User

@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
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
JSpradlin
Advocate II
Advocate II

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.

bcdobbs
Community Champion
Community Champion

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



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.