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.
Using a data set with a structure like below, I am trying to determine if a value related to a certain field, in this case id, is either rising or falling. We are using the most recent value and the value prior to the last one to determine that. In the example below, the latest value is 2 and the previous value is 0, so the change metric on this would equal 2, so the value would be defined as "rising".
personid | Date | Value | |
10 | 1/14/2020 | 0 | |
10 | 1/29/2020 | 1 | |
10 | 2/13/2020 | 1 | |
10 | 2/28/2020 | 2 | |
10 | 3/14/2020 | 1 | |
10 | 3/29/2020 | 0 | |
10 | 4/13/2020 | 2 |
personid | Date | Value | date_rank | latest_value | previous_value | Rising Falling Stable |
10 | 1/14/2020 | 0 | 7 | |||
10 | 1/29/2020 | 1 | 6 | |||
10 | 2/13/2020 | 1 | 5 | |||
10 | 2/28/2020 | 2 | 4 | |||
10 | 3/14/2020 | 1 | 3 | |||
10 | 3/29/2020 | 0 | 2 | 0 | 0 | |
10 | 4/13/2020 | 2 | 1 | 2 | 2 |
Do anyone know how I can get the previous and current metrics into the same row in this table so I can get a change since previous date metric to be showing in the Rising Falling Stable column?
Solved! Go to Solution.
Here is a measure expression you can use in a Table visual with the PersonID column to get your desired result.
Trend =
VAR latestdate =
MAX ( 'Cases'[Date] )
VAR prevdate =
CALCULATE ( MAX ( 'Cases'[Date] ), 'Cases'[Date] < latestdate )
VAR latestvalue =
CALCULATE ( SUM ( 'Cases'[Value] ), 'Cases'[Date] = latestdate )
VAR prevvalue =
CALCULATE ( SUM ( 'Cases'[Value] ), 'Cases'[Date] = prevdate )
RETURN
SWITCH (
TRUE (),
latestvalue > prevvalue, "Rising",
latestvalue < prevvalue, "Falling",
"Stable"
)
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Here is a measure expression you can use in a Table visual with the PersonID column to get your desired result.
Trend =
VAR latestdate =
MAX ( 'Cases'[Date] )
VAR prevdate =
CALCULATE ( MAX ( 'Cases'[Date] ), 'Cases'[Date] < latestdate )
VAR latestvalue =
CALCULATE ( SUM ( 'Cases'[Value] ), 'Cases'[Date] = latestdate )
VAR prevvalue =
CALCULATE ( SUM ( 'Cases'[Value] ), 'Cases'[Date] = prevdate )
RETURN
SWITCH (
TRUE (),
latestvalue > prevvalue, "Rising",
latestvalue < prevvalue, "Falling",
"Stable"
)
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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 |
---|---|
46 | |
28 | |
22 | |
12 | |
8 |
User | Count |
---|---|
76 | |
51 | |
44 | |
16 | |
12 |