Using DAX in PowerPivot to find out value of a column based on MAX / IF another

Question

I'm trying to write a DAX formula that finds the maximum YearMonth and returns the liability IF the points earned are >0. So in the example below the returning value would be 190.

I've spent ages trying to get this to work without any luck, so if anyone can help I'd be really grateful

+-----------+---------------+-----------+
| YearMonth | Points Earned | Liability |
+-----------+---------------+-----------+
|    200506 |            86 |       170 |
|    200507 |            22 |       150 |
|    200508 |            45 |       190 |
|    200509 |             0 |       190 |
|    200510 |             0 |       190 |
+-----------+---------------+-----------+

*Apologies about the formatting of the table, I couldn't work out how to insert it correctly.

Answer 1

Create two measures MaxYearMonth and LastLiability.

MaxYearMonth :=
CALCULATE (
    MAX ( [YearMonth] ),
    FILTER ( ALL ( Table ), [Points Earned] > 0 )
)

LastLiability :=
CALCULATE (
    SUM ( Table[Liability] ),
    FILTER ( Table, Table[YearMonth] = [MaxYearMonth] )
)

The LastLiability measure calculates the Liability by using the MaxYearMonth measure. MaxYearMonth measure calculates the last YearMonth which Points Earned value is greater than 0.

Let me know if this helps.