Returning the Max Date for a set, with an added filter

[ehmacc]

I am trying to create a new column on a table that is a Max calculation with a filter- in this instance, I need to return the Max Date but only if the Rate Change was grater than 0 (example table below).

Right now I am using an equation that returns the Max Date for Each ID, but I need to filter that return to only be if that date was associated with an increase 

Current equation-

Most Recent Rate Change = CALCULATE(max (EmploymentHistory[Date]), ALLEXCEPT(EmploymentHistory, EmploymentHistory[Id]))

ID	Date	Rate Change	Current Date returned	Most Recent Rate Change (DESIRED RESULT)
1	7/3/19	0%	7/3/19	6/8/19
1	6/8/19	4%	7/3/19	6/8/19
1	5/1/18	3%	7/3/19	6/8/19
2	8/8/19	2%	8/8/19	8/8/19
2	2/17/18	7%	8/8/19	8/8/19

I have tried to build the filter into the equation in multiple ways, and keep failing, I need it as part of the equation rather than a filter on a visual or table becuase it will be built used 

[lc_finance · Accepted solution]

Hi @ehmacc ,

You can use the following formula:

Most recent rate change (calculated) = 

CALCULATE(LASTDATE('EmploymentHistory'[Date]), ALLEXCEPT('EmploymentHistory','EmploymentHistory'[ID]), 'EmploymentHistory'[Rate Change]>0)

Basically I added one more condition to the CALCULATE filter: rate should be higher than 0.

Here is a screenshot of what it looks like.

Finally, here is a Power BI file with the new formula.

Does this help you? Let me know if you have further questions,

LC

Interested in Power BI and DAX templates? Check out my blog at www.finance-bi.com

[Nathaniel_C]

Hi @ehmacc ,
You were almost there. Just needed a condition. Let me know if this works for you...it is a measure

Most Recent Rate Change = CALCULATE(MAX(EmploymentHistory[Date]),Filter(ALLEXCEPT(EmploymentHistory,EmploymentHistory[ID]),EmploymentHistory[Rate Change]<>0))

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

[ehmacc]

Thank you so much!