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

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 26 members 952 guests
Please welcome our newest community members: