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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Anonymous
Not applicable

Tagging latest week in calculated column

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. 

Screenshot 2021-09-13 194330.png

 

1 ACCEPTED SOLUTION

@Anonymous 

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

1.PNG

 





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

Proud to be a Super User!




View solution in original post

5 REPLIES 5
ryan_mayu
Super User
Super User

@Anonymous 

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





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

Proud to be a Super User!




Anonymous
Not applicable

all others, should be blank

@Anonymous 

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

1.PNG

 





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

Proud to be a Super User!




Anonymous
Not applicable

thanks for the quick support 🙂

you are welcome





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

Proud to be a Super User!




Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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