Forum Discussion

LouiseR's avatar
LouiseR
Regular Visitor
2 years ago
Solved

Add Icons based on Variance from Previous Day

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 li...
  • ValtteriN's avatar
    ValtteriN
    2 years ago

    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/
Anonymous's avatar
Anonymous
Not applicable
2 years ago

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.