topic Re: Dax to show % change from highest value to second highest in DAX Commands and Tips

Dax to show % change from highest value to second highest

NB689 — Tue, 27 Sep 2022 21:34:38 GMT

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

Re: Dax to show % change from highest value to second highest

amitchandak — Wed, 28 Sep 2022 01:46:42 GMT

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)) )

Re: Dax to show % change from highest value to second highest

NB689 — Wed, 28 Sep 2022 15:30:41 GMT

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?

Re: Dax to show % change from highest value to second highest

NB689 — Wed, 28 Sep 2022 16:16:33 GMT

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.

Re: Dax to show % change from highest value to second highest

Anonymous — Thu, 29 Sep 2022 09:38:29 GMT

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.

Re: Dax to show % change from highest value to second highest

NB689 — Thu, 29 Sep 2022 13:40:17 GMT

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.