DAX to get latest date of an account name entry

Question

I have found similar reported questions, but none of the solutions have worked so far. If I am entering the information incorrectly, please let me know.

I have a table in Power Pivot that contains repeated names, and dates entered that go with the names. What I am trying to do is get the latest date entered for a name.

Example:

Name | Date       | Latest Date
A    | 6/24/2016  |
A    | 6/24/2017  |
A    | 6/24/2018  |
B    | 7/05/2010  |
B    | 7/05/2011  |
C    | 6/8/2009   |
C    | 6/8/2010   |
C    | 6/8/2011   |

What I would like under Latest Date is to have the latest date that corresponds to the Name. It should look like the following:

Name | Date       | Latest Date
A    | 6/24/2016  | 6/24/2018
A    | 6/24/2017  | 6/24/2018
A    | 6/24/2018  | 6/24/2018
B    | 7/05/2010  | 7/5/2011
B    | 7/05/2011  | 7/5/2011 
C    | 6/8/2009   | 6/8/2011 
C    | 6/8/2010   | 6/8/2011 
C    | 6/8/2011   | 6/8/2011

I've tried to use the following function, but all I keep getting are #Errors (of course changing the table reference based on the data in the file)

= CALCULATE(
      MAX(Table1[Date]);
      FILTER(Table1;
          Table1[ID] = EARLIER(Table1[ID])
      )
  )

If the above function is correct, I wonder what I am doing wrong. Which one of the values in the () are column references and which ones are cell references? Perhaps that's where I am going wrong.

Any assistance is very appreciative. I tried to enter the information as nicely as possible so it can be assisted.

Alexis Olson Alexis Olson · Accepted Answer · 2018-11-13T17:44:46

There are no cell or row references in DAX. Everything is done with filters.

Try this for Latest Date.

= CALCULATE(
      MAX(Table1[Date]);
      FILTER(ALL(Table1);
          Table1[Name] = EARLIER(Table1[Name])
      )
  )

Give this a shot as well if that doesn't do the trick:

= CALCULATE(
      MAX(Table1[Date]);
      ALLEXCEPT(Table1; Table1[Name])
  )

DAX to get latest date of an account name entry

1 Answers