How do I compare values in two columns and show an upward arrow if the value has increased?

Questions & Answers
1

How do I compare values in two columns and show an upward arrow if the value has increased. Example: I want to compare value of Q1 2023 and Q2 2023 row by row and show in Q2 2023 that the value has decreased with a red arrow or increased with a green arrow

2

Hi @Salina_Karki

If you have the individual Quarters in Rows, you can click on the measure column and select: Add calculated → Time ago → Previous period change

step1
step11970×1414 338 KB

You can then click on the new change measure and select Edit calculated and copy the formula:
step2
step22084×1242 225 KB

Lastly, create a new measure with a formula like this:

CASE WHEN
    Cache(
        -- paste the copied formula here
    ) > 0
THEN
    "<i class='far fa-arrow-up' style='color:green'></i>"
WHEN
    Cache(
        -- paste the copied formula here
    ) <= 0
THEN
    "<i class='far fa-arrow-down' style='color:red'></i>"
END

Use the Text → Markdown formatting for this measure and you should see green and red arrows in your report :slight_smile:

step3
step31546×1378 182 KB

You can remove the added calculated change measure after everything is working.

Read more about Markdown formatting and font-awesome icons here: Measure formatting (markdown and custom)

​Best regards,
​Nauris

3

Yes this works thanks @nauris.malitis but for the arrows trend I want the quarterly comparison in different columns not rows. Is it possible to get the arrow in the same field as issues created previous period change?

4

Could you share a screenshot of how you intend to build this?
If you could create an example table in Excel or draw up a sketch of this, that would be really helpful!

Thanks!
​Best regards,
​Nauris

5

Here’s a sample
image
I want to see the arrow in Q2, Q3 and so on

6

You can modify the formula to this one:

CASE WHEN
  [Measures].[Issues created] > 0 -- replace with the measure that you will use for the report
THEN
  CoalesceEmpty(Cast(
    [Measures].[Issues created] -- replace with the measure that you will use for the report
    AS STRING
    ),"")||" "||
  CASE WHEN
    Cache(
      -- paste the copied formula here
    ) > 0
  THEN
    "<i class='far fa-arrow-up' style='color:green'></i>"
  WHEN
    Cache(
      -- paste the copied formula here
    ) <= 0
  THEN
    "<i class='far fa-arrow-down' style='color:red'></i>"
  ELSE
    ""
  END
END

And this should give you a report like this:

measure-with-arrows
measure-with-arrows3692×1470 329 KB

​Best regards,
​Nauris

7

This worked, Thank you so much.

1 Like