Create day of week sort based on latest day

[Mishelski]

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

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 

[amehrosh]

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 · Accepted solution]

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:

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

Appreciate your Kudos!! 

LinkedIn | Twitter | Blog | YouTube 

[Mishelski]

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