topic Re: MAX Date for Row in Table but only for certain columns in DAX Commands and Tips

MAX Date for Row in Table but only for certain columns

amart — Thu, 27 Oct 2022 12:54:56 GMT

Hey guys,

I've been working on this for a while and my gut feeling is that i'm missing something obvious here. Relatively knew to DAX but have experience in other programming languages. I'm only working in excel at this point through PowerPivot.

I've got a table with rows of data that that is updated each year. I'm trying to find the last row in each year to display to my user how much time we have until the licnese needs to be renewed. I've gotten close with both the MAX(date) and LASTDATE() function but keep running into an issue.

I want to display the MAX date in the table only in context for certain columns. The issue is when the contractor changes it interprets it as a new row context and gives a new row in the Pivot Table. So in this case I want to ignore the Contractor context.

Below is the ideal result.

Any help would be much appreciated. Also, the other help i've gotten from reading other posts has been great, so thanks for everyone's help direct or indirect!

Re: MAX Date for Row in Table but only for certain columns

Anonymous — Thu, 27 Oct 2022 12:58:05 GMT

Hello you could try

if([license type]= Blank(),Blank(),max([date]))

Then use filter panel to filter out blank values

Re: MAX Date for Row in Table but only for certain columns

amart — Thu, 27 Oct 2022 13:22:15 GMT

Thank you for your response. I had do not repeat on the pivot table settings so it didn't show property. Below is what the actual values are.

So its pulling in the entire row for the prior year date because the contract has changed. In reality I just want the latest row in the table for only Holding, License Type and Entity.

Thanks again for your help.

Re: MAX Date for Row in Table but only for certain columns

amart — Thu, 27 Oct 2022 13:23:53 GMT

This is the full data from the tables.

Table 1

Table 2

Thanks again.

Re: MAX Date for Row in Table but only for certain columns

Anonymous — Thu, 27 Oct 2022 14:17:15 GMT

oh ok i created a sample of your data:

is this the result you want below?"

if yes then this is the formula i used:

Latest Date = CALCULATE(MAX(Amart[Exp. Date]),ALLEXCEPT(Amart,Amart[Contractor]))

Re: MAX Date for Row in Table but only for certain columns

tamerj1 — Thu, 27 Oct 2022 14:24:45 GMT

Hi amart
Please refer to attached sample file with the solution

Exp_Date := VAR CurrentIDTable = CALCULATETABLE ( Table1, ALL ( Table1 ), ALL ( Table2 ), VALUES ( Table1[Holding_ID] ) ) VAR LastRecord = TOPN ( 1, CurrentIDTable, Table1[Date] ) VAR LastContractor = MAXX ( LastRecord, Table1[Contractor] ) VAR CurrentContractor = MAX ( Table1[Contractor] ) RETURN IF ( CurrentContractor = LastContractor, MAX ( Table1[Date] ) )

Re: MAX Date for Row in Table but only for certain columns

amart — Fri, 28 Oct 2022 02:45:32 GMT

That worked! I have a bunch of other tables i'm going to build using the same format! Thanks so much! This community is awesome!

Re: MAX Date for Row in Table but only for certain columns

amart — Fri, 28 Oct 2022 02:46:56 GMT

Thanks for you help here, it wasnt the solution I was looking for in this example, but I had another issue I was working on where this worked great! Thank you!