Rank date column

[Anonymous]

Hi,

Can someone please help writing DAX RANK() / RANKX() using Year and Weeknumber column. Last column "WeekNumber" is the desired column through that function.

TimeID	Date	Year	Month	MonthName	Week	IsWorkingDay	WeekRange	WeeNumber
20200301	01 March 2020	2020	3	March	10	0	01 Mar 20 - 07 Mar 20	Week 1
20200302	02 March 2020	2020	3	March	10	1	01 Mar 20 - 07 Mar 20	Week 1
20200303	03 March 2020	2020	3	March	10	1	01 Mar 20 - 07 Mar 20	Week 1
20200304	04 March 2020	2020	3	March	10	1	01 Mar 20 - 07 Mar 20	Week 1
20200305	05 March 2020	2020	3	March	10	1	01 Mar 20 - 07 Mar 20	Week 1
20200306	06 March 2020	2020	3	March	10	1	01 Mar 20 - 07 Mar 20	Week 1
20200307	07 March 2020	2020	3	March	10	0	01 Mar 20 - 07 Mar 20	Week 1
20200308	08 March 2020	2020	3	March	11	0	08 Mar 20 - 14 Mar 20	Week 2
20200309	09 March 2020	2020	3	March	11	1	08 Mar 20 - 14 Mar 20	Week 2
20200310	10 March 2020	2020	3	March	11	1	08 Mar 20 - 14 Mar 20	Week 2
20200311	11 March 2020	2020	3	March	11	1	08 Mar 20 - 14 Mar 20	Week 2
20200312	12 March 2020	2020	3	March	11	1	08 Mar 20 - 14 Mar 20	Week 2
20200313	13 March 2020	2020	3	March	11	1	08 Mar 20 - 14 Mar 20	Week 2
20200314	14 March 2020	2020	3	March	11	0	08 Mar 20 - 14 Mar 20	Week 2
20200315	15 March 2020	2020	3	March	12	0	15 Mar 20 - 21 Mar 20	Week 3
20200316	16 March 2020	2020	3	March	12	1	15 Mar 20 - 21 Mar 20	Week 3
20200317	17 March 2020	2020	3	March	12	1	15 Mar 20 - 21 Mar 20	Week 3
20200318	18 March 2020	2020	3	March	12	1	15 Mar 20 - 21 Mar 20	Week 3
20200319	19 March 2020	2020	3	March	12	1	15 Mar 20 - 21 Mar 20	Week 3
20200320	20 March 2020	2020	3	March	12	1	15 Mar 20 - 21 Mar 20	Week 3
20200321	21 March 2020	2020	3	March	12	0	15 Mar 20 - 21 Mar 20	Week 3

Many thanks

[Anonymous]

Hi,

I have removed unnecessry columns and wanted to calculate last column through DAX RANK using Year and Week column. 

Date	Year	Month	Week	WeeNumber
01 March 2020	2020	3	10	Week 1
02 March 2020	2020	3	10	Week 1
03 March 2020	2020	3	10	Week 1
04 March 2020	2020	3	10	Week 1
05 March 2020	2020	3	10	Week 1
06 March 2020	2020	3	10	Week 1
07 March 2020	2020	3	10	Week 1
08 March 2020	2020	3	11	Week 2
09 March 2020	2020	3	11	Week 2
10 March 2020	2020	3	11	Week 2
11 March 2020	2020	3	11	Week 2
12 March 2020	2020	3	11	Week 2
13 March 2020	2020	3	11	Week 2
14 March 2020	2020	3	11	Week 2
15 March 2020	2020	3	12	Week 3
16 March 2020	2020	3	12	Week 3
17 March 2020	2020	3	12	Week 3
18 March 2020	2020	3	12	Week 3
19 March 2020	2020	3	12	Week 3
20 March 2020	2020	3	12	Week 3
21 March 2020	2020	3	12	Week 3

[harshnathani]

Hi @Anonymous ,

Create a Calculated Column

Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date])+1

Create another Calculated Column,

Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)

Regards,

Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!

[Anonymous]

Thanks @harshnathani 

It partially works. 

I have date table which contains dates starting from year 2015 till 2040. I just copied a three weeks data.

Reason I wanted to Rank by [Year] and [Week] because every year ranking should start from 1. 

Is it possible to copy data into PBIX file and attach back here. I wish I could attach a full file. 

Many thanks

[mahoneypat · Accepted solution]

Please try this expression in your column.  It calculates the weeks difference within the same year from the minimum date in that year (and concatenates it with "Week ").

WeekNumber = var mindatesameyear = CALCULATE(MIN('Date'[Date]), all('Date'), 'Date'[Year]=EARLIER('Date'[Year]))

var weeksfrommin = ROUNDDOWN(DATEDIFF(mindatesameyear, 'Date'[Date],DAY)/7,0)+1

return "Week " & weeksfrommin

If this works for you, please mark it as the solution.  Kudos are great too.  Please let me know if it doesn't or if any questions.
Regards,
Pat

[Anonymous]

Thanks a lot @mahoneypat, Yes it works nicely but can you please elaborate DAX a bit. 

Many thanks

[mahoneypat]

Glad it worked for your use case.  I used daxformatter.com to format it better and added comments to show how it works.

WeekNumber =
VAR mindatesameyear =
CALCULATE (
MIN ( 'Date'[Date] ),
ALL ( 'Date' ),
'Date'[Year] = EARLIER ( 'Date'[Year] )
) //this part finds the minimum date for the same year. the all removes all the filters on Date, and the Earlier function brings back the original Year context
VAR weeksfrommin =
ROUNDDOWN ( DATEDIFF ( mindatesameyear, 'Date'[Date], DAY ) / 7, 0 ) + 1 // this part finds the # days difference from current row date and the min date from line above; divide by 7 to convert to weeks then convert to an integer with Rounddown(#, 0); then adds 1 so we don't start with Week 0
RETURN
"Week " & weeksfrommin
// concatenate "Week" with the determined weeksfrommin

Regards,

Pat