Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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 ,
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)) )
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.
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.
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.
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
21 | |
16 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |