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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
THENNA_41
Post Partisan
Post Partisan

Date column Convert new column Week basics

I am trying to Create a  new column called week based on below logic

  1. If day part of lead_date <=7 -> Wk1
  2. If day part of lead_date  between 8 to 14 -> Wk2
  3. If day part of lead_date between 15 to 21 -> Wk3
  4. If day part of lead_date>21 -> Wk4

Sample Data:

 

trail_id                lead_date

626238              01-08-2021

626231              02-08-2021

626232              03-08-2021

646067              04-08-2021

644770              05-08-2021

--

--

643468              31-08-2021

 

 

My Measure :

WEEK = "Wk"& 1 + WEEKNUM ('Sheet1'[lead_date] ) -WEEKNUM( STARTOFMONTH ('Sheet1'[lead_date]))
 

looking for support .. thanks in advance

1 ACCEPTED SOLUTION
technolog
Super User
Super User

Hi!

You can try create calc column

SWITHC(TRUE(),

  DAY([lead_date]) <= 7, "Week 1",

  DAY([lead_date]) <= 14, "Week 2",

  DAY([lead_date]) <= 21, "Week 3",

  "Week 4"

)

View solution in original post

2 REPLIES 2
THENNA_41
Post Partisan
Post Partisan

@technolog  thank you  its worked 

technolog
Super User
Super User

Hi!

You can try create calc column

SWITHC(TRUE(),

  DAY([lead_date]) <= 7, "Week 1",

  DAY([lead_date]) <= 14, "Week 2",

  DAY([lead_date]) <= 21, "Week 3",

  "Week 4"

)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors