Tagging latest week in calculated column

[ghamiz]

Hi, I have a table having Year, Month, Week.

I want to add another column to give me lastest week of current year as ThisWeek, and other tags as Week-1, Week-2, Week-3.

Data is from 2018 onwards, and latest is of 2021 Week-35, so I want to tag last Wk-32, 33, 34 & 35 of 2021. When new data comes, say of Wk-36, that should be tagged as ThisWeek, and past weeks tags updated. Have been trying for a while, but my code always tags Week-53, the max of all as ThisWeek rather, max of 2021 as this week. Please help. See snip below for code and table. 

[ryan_mayu]

@ghamiz 

what's the expected result for 2020 Dec and 2019 Dec weeks?

[ghamiz]

all others, should be blank

[ryan_mayu · Accepted solution]

@ghamiz 

you can try to create a yearweek column and then create last three week column

Yearweek = 'Table'[Year]*100+'Table'[Week]

Column = 
VAR _rank=RANKX(all('Table'),'Table'[Yearweek])-1
return if(_rank=0,"This week", if(_rank<4,"Week-"&_rank))

[ghamiz]

thanks for the quick support 🙂

[ryan_mayu]

you are welcome