topic Re: Create day of week sort based on latest day in DAX Commands and Tips

Create day of week sort based on latest day

Mishelski — Tue, 08 Nov 2022 11:35:16 GMT

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

Re: Create day of week sort based on latest day

VahidDM — Tue, 08 Nov 2022 22:15:27 GMT

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

Re: Create day of week sort based on latest day

amehrosh — Thu, 10 Nov 2022 07:10:37 GMT

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))

))

Re: Create day of week sort based on latest day

Mishelski — Thu, 10 Nov 2022 09:16:11 GMT

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