Forum Discussion
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.
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])
Once you add the max date calculated column, add another similar to
rolling week = DATEDIFF(Sheet1[week], Sheet1[max date], WEEK) + 1
3 Replies
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.
Once you add the max date calculated column, add another similar to
rolling week = DATEDIFF(Sheet1[week], Sheet1[max date], WEEK) + 1
