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 like this;

 

 

The result needed;

 

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

Appreciate your help!

  • 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/

3 Replies

  • ValtteriN's avatar
    ValtteriN
    Community Champion
    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/
    • LouiseR's avatar
      LouiseR
      Regular Visitor
      2 years ago

      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.

       

  • 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.