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
lguelcher
Regular Visitor

MAX Date

I'm trying to create a Custom Column that returns the latest date from column 'Week'. I've tried the following:

 

=Table.AddColumn(table, "MAX Date", each List.Max({[Week]}))

However, that formula returns the date that is the Week column instead of just the Max date.

 

I'm not sure what I'm doing wrong.

Thanks.

2 ACCEPTED SOLUTIONS
blopez11
Resident Rockstar
Resident Rockstar

Not sure how to do it in the query editor, but you could try to add a calculated column using the dax function MAXX, i.e. MAX_DATE = MAXX (table_name, [Week])

View solution in original post

Once you add the max date calculated column, add another similar to

rolling week = DATEDIFF(Sheet1[week], Sheet1[max date], WEEK) + 1

View solution in original post

3 REPLIES 3
blopez11
Resident Rockstar
Resident Rockstar

Not sure how to do it in the query editor, but you could try to add a calculated column using the dax function MAXX, i.e. MAX_DATE = MAXX (table_name, [Week])

I thought about doing it as a calculated column, which may end up being best. What I need to end up with is a Rolling Week # based on dates.

 

For example: If the Max Date is 9/4/2016, then I need to know what week number it is for the dates in the [Week] column.

This is what I have in an Excel Data Model. The Rolling Week # is the column I need in the end.

 

maxdate.JPG

Once you add the max date calculated column, add another similar to

rolling week = DATEDIFF(Sheet1[week], Sheet1[max date], WEEK) + 1

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.