Add Icons based on Variance from Previous Day

[LouiseR]

Hello there, I need some help.  I am trying to create a matrix (multi-level visual) that has icons (R/A/G) to indicate whether the number went up or down from previous day.

My data table looks like this;

The result needed;

Been looking around for answers but have not found anything that helps my case.

Appreciate your help!

[Anonymous]

Hi @LouiseR ,

I have created a simple sample, please refer to it to see if it helps you.

Create 2 measures. To get the count value before today.

Measure =
CALCULATE (
    MAX ( 'Table'[COUNT] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Region] = SELECTEDVALUE ( 'Table'[Region] )
            && 'Table'[AGEING] = SELECTEDVALUE ( 'Table'[AGEING] )
            && 'Table'[Date] < SELECTEDVALUE ( 'Table'[Date] )
    )
)

Then compare the 2 results.

result =
IF (
    MAX ( 'Table'[COUNT] ) = [Measure],
    0,
    IF (
        MAX ( 'Table'[COUNT] ) < [Measure],
        1,
        IF ( MAX ( 'Table'[COUNT] ) > [Measure], 2 )
    )
)

 Then create the conditional formatting.

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

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

[ValtteriN · Accepted solution]

Hi,

Here is how to do this:

Data:

Dax:

Test for previous =

var _p = MAX('Table (15)'[Date])

var _cv = MAX('Table (15)'[Value])

var _pv = CALCULATE(MAX('Table (15)'[Value]),ALL('Table (15)'[Date]),'Table (15)'[Date]<_p)

RETURN

SWITCH(TRUE(),

_cv=_pv,0, //same

_cv<_pv,1, //up

_cv>_pv,2) //down

Add rule:
End result:
If you don't want arrows on the first row change the dax:

Test for previous =

var _p = MAX('Table (15)'[Date])

var _cv = MAX('Table (15)'[Value])

var _pv = CALCULATE(MAX('Table (15)'[Value]),ALL('Table (15)'[Date]),'Table (15)'[Date]<_p)

RETURN

SWITCH(TRUE(),

_cv=_pv,0, //same

_cv<_pv,1, //up

_pv=BLANK(),BLANK(),

_cv>_pv,2) //down

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

[LouiseR]

Your solution is great, however in my Matrix visual - it is the sum of the count.

The results look weird and even if the numbers go up or down it doesn't change the icons/results.

Below is the image of the Data table as well.

 Appreciate your help on this.