MAX Date

[lguelcher]

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.

[blopez11 · Accepted solution]

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 · Accepted solution]

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]

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.

[blopez11 · Accepted solution]

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

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