Dax to show % change from highest value to second highest

[NB689]

Hello I have the below data and I would like to create a card that show the percent change from the highest date value (9/18) to the second highest (9/17). So my intention would to be a value that shows -3.077 %.

Date	Hours Logged In
9/16/2022	9.5
9/17/2022	6.5
9/18/2022	6.3

Hours Logged In is a calculated measure in the table "Non Overlapping" and Date is in the Date Range table with a relationship tied to the other table.

Thank you

[amitchandak]

@NB689 ,

Try measure like

Var _max = maxx(allselected(Table), Table[Date])

Var _min = maxx(filter( allselected(Table), Table[Date] <_max), Table[Date] )

return

Divide( calculate( sum(Table[Value]), filter('Table', 'Table'[Date] =_max)) - calculate( sum(Table[Value]), filter('Table', 'Table'[Date] =_min)) ,calculate( sum(Table[Value]), filter('Table', 'Table'[Date] =_min))  )

[NB689]

I made some more progress by using Sumx instead of Sum since my value is a measure. But my calculation is way off at 45%. Also I split this out into 3 different measures instead of using variables to help me troubleshoot. The max and min login days are working as they should. 

[Anonymous · Accepted solution]

Hi @NB689 ,

I checked your code and I think you need to use ALL() function in your filter.

Login Change by Day =
VAR _HourofMax =
    SUMX (
        FILTER ( ALL ( 'Date Range' ), 'Date Range'[Date] = [Max Login Date] ),
        [Hours Logged In]
    )
VAR _HourofMin =
    SUMX (
        FILTER ( ALL ( 'Date Range' ), 'Date Range'[Date] = [Min Date] ),
        [Hours Logged In]
    )
RETURN
    DIVIDE ( _HourofMax - _HourofMin, _HourofMin )

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[NB689]

That is working great now, thank you! Just trying to build my understanding, how do you know when you need to apply ALL when using a filter? I think this is due to my 'Hours Logged In' being a measure.

[NB689]

I'm not able to pull my value into the return expression. I believe since it is a measure that represents a column. Not sure if I need to add a SELECTED statement for that to work?