compare day vs day with conditional formatting

[Anonymous]

Hii,.

As the pic below (ex : in excel) I need to compare the today vs yesterday (ex : 29 Nov vs 28 Nov / 31 Nov vs 30 Nov / 1 Dec vs 30 Nov etc.)

expected result : the arrow in the matrix show data up/equal/down, today compared to yesterday.

please explain in detail, iam beginner on dax.  thanks before

Ex : Expected Matrix 

ex :  RAW data

Transcation Date	Region	Actual
11/28/2021	A	1
11/29/2021	B	10
11/30/2021	B	11
12/1/2021	B	11
12/2/2021	A	3
12/3/2021	A	2
12/4/2021	A	54
12/5/2021	C	1
12/6/2021	C	25
12/7/2021	C	15
12/8/2021	D	5
12/9/2021	E	6
12/10/2021	F	5
12/11/2021	F	15
12/12/2021	G	1
12/13/2021	H	1
12/14/2021	L	1
12/15/2021	M	8
12/16/2021	N	1

[Anonymous]

woooow you're awesome both!! very helpful

thank you very much!!

[VahidDM · Accepted solution]

Hi @Anonymous 

Try this code to find the variance for each Date vs Yesterday:

Arrow = 
Var _SelectedDate = max('Table'[Transcation Date]) --this will return the column date
Var  _Yesterday = _SelectedDate-1 -- this will return the yesterday date
Var _SelectedDateValue = sum('Table'[Actual]) -- this will return the column date actual value
Var __YesterdayValue = CALCULATE(sum('Table'[Actual]),removefilters('Table'[Transcation Date]),'Table'[Transcation Date]=_Yesterday)
return
if(ISBLANK(__YesterdayValue),0,__YesterdayValue-_SelectedDateValue) -- if th value of Yesterday is blank it will show Zero because nothing changed 

The output will be like this:

Now you need to set a conditional formatting based on this measure [Arrow]:

select Icon on the conditional formatting of Arrow in the Value section of the Matrix visual:

set this:

Output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[amitchandak]

@Anonymous , With help from date table, you can get day on day diff %

This Day = sum('Table'[Qty])

or

This Day = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])))

Last Day = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])-1))

or

Last Day = CALCULATE(sum('Table'[Qty]), previousday('Date'[Date]))

diff %=divide([This Day] - [Last Day], [Last Day])

In conditional formatting you have option for icon formatting that you can use

https://exceleratorbi.com.au/conditional-formatting-using-icons-in-power-bi/
https://community.powerbi.com/t5/Desktop/FORMAT-icon-set-for-use-in-a-data-card/td-p/811692

Also using unichar you can create a arrow measure and color that using color measure in conditional formatting field value option

/////Arrow
Arrow =
var _change =[Net Sales YTD]-[Net Sales LYTD]
return
SWITCH (
TRUE(),
_change > 0, UNICHAR(9650),
_change = 0, UNICHAR(9654),
_change < 0, UNICHAR(9660)
)

/////Arrow Color
Arrow color =
var _change =[Net Sales YTD]-[Net Sales LYTD]
return
SWITCH (
TRUE(),
_change > 0, "green",
_change = 0, "blue",
_change < 0, "red"
)

UNICHAR - Tool for Custom Icon Formatting: https://www.youtube.com/watch?v=veCtfP8IhbI&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=50