cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
lguelcher Frequent Visitor
Frequent 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

Accepted Solutions
blopez11 Established Member
Established Member

Re: MAX Date

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

blopez11 Established Member
Established Member

Re: MAX Date

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

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

3 REPLIES 3
blopez11 Established Member
Established Member

Re: MAX Date

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

lguelcher Frequent Visitor
Frequent Visitor

Re: MAX Date

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

blopez11 Established Member
Established Member

Re: MAX Date

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

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