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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Geo_Nelson
Frequent Visitor

Next date from day of week number

Need to add a calulated column that returns a date based on day of week number from a given date.

 

Example:

Process Date (given date): Friday 09/09/22

Route Departure Day (day of week number): 2

Need calulation to return: Monday 09/12/22

 

thanks, geo

1 ACCEPTED SOLUTION

@Geo_Nelson Sorry, missed a paren:

Departure Date = 
  VAR __Date = MAX('Table'[Process Date])
  VAR __DeptDay = MAX('Table'[Route Departure Day]
  VAR __Table = ADDCOLUMNS(CALENDAR(__Date,__Date+7),"WeekdayNum",WEEKDAY([Date]) )
RETURN
  MINX(FILTER(__Table,[WeekdayNum] = __DeptDay),[Date])

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

4 REPLIES 4
v-rzhou-msft
Community Support
Community Support

Hi @Geo_Nelson ,

 

I think you can try this code to create a calculated column.

Next date = 'Table'[Process Date] + 'Table'[Route Departure Day] + 1

 Result is as below.

RicoZhou_0-1663571964073.png

 

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Greg_Deckler
Super User
Super User

@Geo_Nelson Try:

Departure Date = 
  VAR __Date = MAX('Table'[Process Date])
  VAR __DeptDay = MAX('Table'[Route Departure Day]
  VAR __Table = ADDCOLUMNS(CALENDAR(__Date,__Date+7),"WeekdayNum",WEEKDAY([Date])
RETURN
  MINX(FILTER(__Table,[WeekdayNum] = __DeptDay),[Date])

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler I'm sure I'm doing something wrong on my end. 

Geo_Nelson_0-1663359407361.png

 

@Geo_Nelson Sorry, missed a paren:

Departure Date = 
  VAR __Date = MAX('Table'[Process Date])
  VAR __DeptDay = MAX('Table'[Route Departure Day]
  VAR __Table = ADDCOLUMNS(CALENDAR(__Date,__Date+7),"WeekdayNum",WEEKDAY([Date]) )
RETURN
  MINX(FILTER(__Table,[WeekdayNum] = __DeptDay),[Date])

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.