Search code examples
maxpowerpivotdaxmaxifs

Powerpivot: Retrieve max value for a group in a related table

I have 2 tables with a one-to-many relationship.

-TableGroup: table with groupletter

-TableAll: table with unique identifier, groupletter, a date

Problem: I want to get the max value of the date from TableAll into a new column in TableGroup. See below.

Question: What is the formula for column MAXdate? 

TableAll:
ID |  Group  |     date                
1       A        4/01/2017
2       A        2/10/2016
3       A        2/06/2016
4       B        2/12/2016
5       B        15/12/2016
6       B        2/03/2017
7       C        5/02/2016
8       C        16/01/2016

TableGroup:
Group  |     MAXdate                
A           4/01/2017
B           2/03/2017
C           5/02/2016

The below formula doesn't work: See here

Solution

  • The answer is:

    CALCULATE (
    MAX ( TableAll[Date] ),
    FILTER ( TableAll, TableAll[Group] = EARLIER ( TableGroup[Group] ) )
)