Forum Discussion
Helper IVMatrix Column last Date comparison
Hi
i have a matrix that has departments in rows and months in columns
i wanted to add some conditional formatting at the end column and check to see if the last column (excluding the column total) is higher than the previous month.
I tried
CondFormatBarsLast2Months =
VAR LastMonth = Max(Calender_Lookup[Date])
VAR Last2Month = Datesadd(LastMonth, -1, Month)
Return
IF(LastMonth > Last2Month, "Green")
but having no luck
i will run into 2 problems if
1) User Selects January as previous month will be December previos year
2) if there is only 1 month in the visual
2)
Mahmed1 , You can create a color measure like given below and use that in conditional formatting in field value option
/////Arrow Color
Arrow color =
var _change =[MTD Sales ]-[Last MTD Sales ]
return
SWITCH (
TRUE(),
_change > 0, "green",
_change = 0, "blue",
_change < 0, "red"
)You can get this month vs last month using Time intelligence and date table
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 Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))How to do conditional formatting by measure and apply it on pie?
https://www.youtube.com/watch?v=RqBb5eBf_I4&list=PLPaNVDMhUXGYo50Ajmr4SgSV9HIQLxc8L
https://community.powerbi.com/t5/Community-Blog/Power-BI-Conditional-formatting-the-Pie-Visual/ba-p/1682539
https://amitchandak.medium.com/power-bi-where-is-the-conditional-formatting-option-in-new-format-pane-66e0afcb15f3Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s
2 Replies
- amitchandak
Super User
Mahmed1 , You can create a color measure like given below and use that in conditional formatting in field value option
/////Arrow Color
Arrow color =
var _change =[MTD Sales ]-[Last MTD Sales ]
return
SWITCH (
TRUE(),
_change > 0, "green",
_change = 0, "blue",
_change < 0, "red"
)You can get this month vs last month using Time intelligence and date table
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 Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))How to do conditional formatting by measure and apply it on pie?
https://www.youtube.com/watch?v=RqBb5eBf_I4&list=PLPaNVDMhUXGYo50Ajmr4SgSV9HIQLxc8L
https://community.powerbi.com/t5/Community-Blog/Power-BI-Conditional-formatting-the-Pie-Visual/ba-p/1682539
https://amitchandak.medium.com/power-bi-where-is-the-conditional-formatting-option-in-new-format-pane-66e0afcb15f3Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s 
Mahmed1Thank you, i was wondering if we could have done something to get Last Column like how we can use the new Index function that allows you to give values from a certain position in a paticular row but im ot not sure if that is an option in just yet?