topic Re: Add Icons based on Variance from Previous Day in DAX Commands and Tips

Add Icons based on Variance from Previous Day

LouiseR — Tue, 05 Dec 2023 08:56:00 GMT

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!

Re: Add Icons based on Variance from Previous Day

ValtteriN — Tue, 05 Dec 2023 11:18:52 GMT

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/

Re: Add Icons based on Variance from Previous Day

Anonymous — Thu, 28 Dec 2023 06:27:31 GMT

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.

Re: Add Icons based on Variance from Previous Day

LouiseR — Wed, 15 May 2024 04:08:08 GMT

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.