Forum Discussion
Previous month comparison
Hi Folks,
I am looking for solution to compare previous to current month and show indicator arrow up or down in PBI.
I have date in column, below is the snapshot which i am looking for a solution. Thanks in advance.
Shruthi96 , One you can use icon formatting. Second, you can use icon measure and color measure. Color measure will used in conditional formatting using field value option
measures
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))this month = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH('Date'[Date])))
/////Arrow
Arrow =
var _change =[This month]-[Last Month]
return
SWITCH (
TRUE(),
_change > 0, UNICHAR(9650),
_change = 0, UNICHAR(9654),
_change < 0, UNICHAR(9660)
)
/////Arrow Color
Arrow color =
var _change =[This month]-[Last Month]
return
SWITCH (
TRUE(),
_change > 0, "green",
_change = 0, "blue",
_change < 0, "red"
)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
https://exceleratorbi.com.au/dax-unichar-function-power-bi/thanks amitchandak
2 Replies
Shruthi96 , One you can use icon formatting. Second, you can use icon measure and color measure. Color measure will used in conditional formatting using field value option
measures
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))this month = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH('Date'[Date])))
/////Arrow
Arrow =
var _change =[This month]-[Last Month]
return
SWITCH (
TRUE(),
_change > 0, UNICHAR(9650),
_change = 0, UNICHAR(9654),
_change < 0, UNICHAR(9660)
)
/////Arrow Color
Arrow color =
var _change =[This month]-[Last Month]
return
SWITCH (
TRUE(),
_change > 0, "green",
_change = 0, "blue",
_change < 0, "red"
)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
https://exceleratorbi.com.au/dax-unichar-function-power-bi/thanks amitchandak