Trying to Find One Value Based on the Latest Date in Another Cell. Grouped By ID

[Anonymous]

I have one ID column with nonunique IDs.

I have another column with dates.

I have a third column that has a 0 or 1 depending on a condition.

For each ID, I am trying to find the 0 or 1 value for the latest date in the date column grouped by ID and add that for each row for that ID in a new column. I'm trying to create the "Most Recent Active Status" in the below example:

ID	Dates	Active Status at Date	Most Recent Active Status
1	11/1/2013	1	0
1	11/1/2015	0	0
1	5/1/2015	1	0
2	11/1/2012	1	1
2	11/1/2014	1	1
3	5/1/2014	0	1
3	11/1/2013	1	1
3	11/1/2015	1	1
3	5/1/2015	0	1

[AiolosZhao · Accepted solution]

Hi @Anonymous ,

Please try to use the below measure, it works on my desktop:

Measure 8 = CALCULATE(MAX('Table (2)'[Most Recent Active Status]),FILTER(ALL('Table (2)'[Dates]),'Table (2)'[Dates] = CALCULATE(MAX('Table (2)'[Dates]),ALLEXCEPT('Table (2)','Table (2)'[ID]))))

Aiolos Zhao

[amitchandak · Accepted solution]

Try new columns

Max date = maxx(filter(table,table[ID] = earlier(table[ID])  ),table[Date])	
Max Status = maxx(filter(table,table[ID] = earlier(table[ID])   && table[date] = earlier(table[Max date ])),table[status])	

[v-kelly-msft · Accepted solution]

Hi @Anonymous ，

You can create a measure as below:

Measure = 
var a =CALCULATE(MAX('Table'[Dates]),ALLEXCEPT('Table','Table'[ID]))
Return
CALCULATE(MAX('Table'[Active Status at Date]),'Table'[Dates]=a)

And you will see:

Or you can create a calculated column as below:

Column = 
var a =CALCULATE(MAX('Table'[Dates]),ALLEXCEPT('Table','Table'[ID]))
var b= CALCULATE(MAX('Table'[Active Status at Date]),FILTER('Table','Table'[ID]=EARLIER('Table'[ID])),'Table'[Dates]=a)
Return
b

And you will see:

For the related .pbix file,pls click here.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

@v-kelly-msftThank you for you reply!

I used the calulated column approach since I'm not too familiar with measures. It worked beautifully. It was truly an elegant solution with those variables.

I'm still trying to get my head around the EARLIER() function.

Thanks for your help!

[amitchandak · Accepted solution]

Try new columns

Max date = maxx(filter(table,table[ID] = earlier(table[ID])  ),table[Date])	
Max Status = maxx(filter(table,table[ID] = earlier(table[ID])   && table[date] = earlier(table[Max date ])),table[status])	

[Anonymous]

@amitchandakThank you for taking the time to respond. Your solution worked! I'm still trying to understand what EARLIER() does, but from playing with the code, it seems like it applies the result of the equation to all rows that meet the filter condition(s) rather than just the row where the maxx() is found. 

[AiolosZhao · Accepted solution]

Hi @Anonymous ,

Please try to use the below measure, it works on my desktop:

Measure 8 = CALCULATE(MAX('Table (2)'[Most Recent Active Status]),FILTER(ALL('Table (2)'[Dates]),'Table (2)'[Dates] = CALCULATE(MAX('Table (2)'[Dates]),ALLEXCEPT('Table (2)','Table (2)'[ID]))))

Aiolos Zhao

[Anonymous]

@AiolosZhaoThank you for taking the time to respond to my question. I tried your solution, and it worked! I'm still trying to understand how measures differ from calculated columns. It looks like one cannot use measures in the Data view, but measures can be used when creating a table in the Report view. Is that right?