MAX Date for Row in Table but only for certain columns

[amart]

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!

[tamerj1 · Accepted solution]

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

[amart]

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!

[Anonymous]

Hello you could try

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

Then use filter panel to filter out blank values

[amart]

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. 

[Anonymous]

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

[amart]

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!

[amart]

This is the full data from the tables. 

Table 1

Table 2

Thanks again.