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

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
Mishelski
Frequent Visitor

Create day of week sort based on latest day

I have a table where I am trying to create a coloumn with values from 1 to 7 based on monday to sunday and where the latest date is 1 and the day before that = 2 and so on

Mishelski_0-1667907009143.png

 

I have used the following calculation to create this but its not doing what im expecting. Im not sure why Sat is showing as -4 rather than 3

 

DOW sort flag =
VAR daycheck = weekday(today(),2) - 'DIM_WEEKDAY_sort'[WEEKDAY]
Return
if(daycheck = -1,6,
if(daycheck = 0,7,
if(daycheck = -2,5,
if(daycheck = -3,4,
daycheck))
))

 

Any help would be appraciated 

 

1 ACCEPTED SOLUTION
VahidDM
Super User
Super User

Hi @Mishelski 

 

Try this code to add your column:

DOW sort flag =
VAR _daycheck =
    WEEKDAY ( TODAY (), 2 )
RETURN
    IF (
        DIM_WEEKDAY_sort[WEEKDAY] >= _daycheck,
        7 - ( DIM_WEEKDAY_sort[WEEKDAY] - _daycheck ),
        _daycheck - DIM_WEEKDAY_sort[WEEKDAY]
    )

 

Output:

VahidDM_0-1667945718043.png

 

 

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!! 

Badges.jpg

LinkedIn | Twitter | Blog | YouTube 

View solution in original post

3 REPLIES 3
amehrosh
Frequent Visitor

Hi,
Please try this once...
Hope it works..!!!
 
DOW sort flag =
VAR daycheck = DIM_WEEKDAY_sort'[WEEKDAY]-(weekday(today(),2)-1)
Return
if(daycheck = -1,6,
if(daycheck = 0,7,
if(daycheck = -2,5,
if(daycheck = -3,4,
daycheck))
))
VahidDM
Super User
Super User

Hi @Mishelski 

 

Try this code to add your column:

DOW sort flag =
VAR _daycheck =
    WEEKDAY ( TODAY (), 2 )
RETURN
    IF (
        DIM_WEEKDAY_sort[WEEKDAY] >= _daycheck,
        7 - ( DIM_WEEKDAY_sort[WEEKDAY] - _daycheck ),
        _daycheck - DIM_WEEKDAY_sort[WEEKDAY]
    )

 

Output:

VahidDM_0-1667945718043.png

 

 

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!! 

Badges.jpg

LinkedIn | Twitter | Blog | YouTube 

Thank you so much! This has worked perfectly! Really appreciate it!

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

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

August 2025 community update carousel

Fabric Community Update - August 2025

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