CODEWITHSUNDEEP
powerbipowerbi-desktop
Shivam Sarin
Shivam Sarin

Reputation: 551

Power BI Matrix/Table visual. Compare values and highlight the greater value

I have a data table in which I have a date column and cases. I'm not sure if this is possible in Power Bi, but what I want to achieve is a matrix/table visual comparing values of different years. For example, compare the values of this year's first week of January with last year's first week of January and highlight the current year's value if it's greater than last year's.

In the attached screenshot below, I have a count of cases in each week from last year. I want to compare the 2022 Jan first week value(618) with the 2023 Jan first week value(577) and highlight the current year's value if it's greater than last year's value.

I want to continue the comparison till the current date. So it goes like the first week of 2022 vs the first week of 2023, 2nd week of 2022 vs 2nd week of 2023, and so on.

enter image description here

Upvotes: 0

Views: 655

Answers (1)

Ashok Anumula
Ashok Anumula

Reputation: 1515

First create a week_of_month column using calculated column in dax

week_of_year = weeknum(Data[Date])
week_of_first_day_of_month = WEEKNUM(date(Data[Date].[year],Data[Date].[MonthNo],1))
week_of_month = 1 + Data[week_of_year] - Data[week_of_first_day_of_month]

enter image description here

write a measure Highlight

    Highlight = 
var val1 = calculate(sum(Data[Score]), filter(all(Data), Data[Month Name] = SELECTEDVALUE(Data[Month Name]) && Data[week_of_month] = SELECTEDVALUE(Data[Weekofmonth]) && Data[Year] = 2022))
var val2 = calculate(sum(Data[Score]), filter(all(Data), Data[Month Name] = SELECTEDVALUE(Data[Month Name]) && Data[week_of_month] = SELECTEDVALUE(Data[Weekofmonth]) && Data[Year] = 2023))

return if((max(Data[Year]) = 2022) && (val1 > val2), 1, 0)

Then do conditional formatting using Highlight measure enter image description here enter image description here

Upvotes: 1

Related Questions