Conditional formatting when the measure used for formatting contains WEEKNUM

[Anonymous]

Hello,

I have an issue with conditional formatting when using two measures. I want to format the values of my table (where the coloumns are the last 10 weeks) by using the value of a measure.

Values of the table:

ClaimCount = Sum(Claims)

First measure that i use as a 'target' value is the average of the last 10 weeks:

Avg 10w. claims  = 
var _weeks10=CALCULATE( [ClaimCount],Dim_date[WeekNo]=WEEKNUM(TODAY(),2)-10 )
var _weeks9=CALCULATE( [ClaimCount],Dim_date[WeekNo]=WEEKNUM(TODAY(),2)-9 )
var _weeks8=CALCULATE( [ClaimCount],Dim_date[WeekNo]=WEEKNUM(TODAY(),2)-8 )
var _weeks7=CALCULATE( [ClaimCount],Dim_date[WeekNo]=WEEKNUM(TODAY(),2)-7 )
var _weeks6=CALCULATE( [ClaimCount],Dim_date[WeekNo]=WEEKNUM(TODAY(),2)-6 )
var _weeks5=CALCULATE( [ClaimCount],Dim_date[WeekNo]=WEEKNUM(TODAY(),2)-5 )
var _weeks4=CALCULATE( [ClaimCount],Dim_date[WeekNo]=WEEKNUM(TODAY(),2)-4 )
var _weeks3=CALCULATE( [ClaimCount],Dim_date[WeekNo]=WEEKNUM(TODAY(),2)-3  )
var _weeks2=CALCULATE( [ClaimCount],Dim_date[WeekNo]=WEEKNUM(TODAY(),2)-2  )
var _weeks1=CALCULATE( [ClaimCount],Dim_date[WeekNo]=WEEKNUM(TODAY(),2) -1 )
return
if(
    (_weeks1+_weeks2+_weeks3+_weeks4+_weeks5+_weeks6+_weeks7+_weeks8+_weeks9+_weeks10)/10=blank(),
    " ",
    (_weeks1+_weeks2+_weeks3+_weeks4+_weeks5+_weeks6+_weeks7+_weeks8+_weeks9+_weeks10)/10
)

Then i use this conditional formatting code for the color: 

Color  = 

switch (TRUE(),
    
        [Avg 10w. claims]
        >= [ClaimCount]
        ,
    "#D64550",
    "#089000"
)

 The problem is that it doesnt work, it only shows the hex color for green (#089000), even though some of the values are under the target value and should show red (#D64550).

I have dobble checked, and the Avg. 10 weeks measure shows the correct number on a card (and it is a number). What have i done wrong?

[v-xicai]

Hi @Anonymous ,

You may change your formula like DAX below.

Color =
VAR ClaimCount =
    SUM ( [Claims] )
VAR _weeks10 =
    CALCULATE ( SUM ( [Claims] ), Dim_date[WeekNo] = WEEKNUM ( TODAY (), 2 ) - 10 )
VAR _weeks9 =
    CALCULATE ( SUM ( [Claims] ), Dim_date[WeekNo] = WEEKNUM ( TODAY (), 2 ) - 9 )
VAR _weeks8 =
    CALCULATE ( SUM ( [Claims] ), Dim_date[WeekNo] = WEEKNUM ( TODAY (), 2 ) - 8 )
VAR _weeks7 =
    CALCULATE ( SUM ( [Claims] ), Dim_date[WeekNo] = WEEKNUM ( TODAY (), 2 ) - 7 )
VAR _weeks6 =
    CALCULATE ( SUM ( [Claims] ), Dim_date[WeekNo] = WEEKNUM ( TODAY (), 2 ) - 6 )
VAR _weeks5 =
    CALCULATE ( SUM ( [Claims] ), Dim_date[WeekNo] = WEEKNUM ( TODAY (), 2 ) - 5 )
VAR _weeks4 =
    CALCULATE ( SUM ( [Claims] ), Dim_date[WeekNo] = WEEKNUM ( TODAY (), 2 ) - 4 )
VAR _weeks3 =
    CALCULATE ( SUM ( [Claims] ), Dim_date[WeekNo] = WEEKNUM ( TODAY (), 2 ) - 3 )
VAR _weeks2 =
    CALCULATE ( SUM ( [Claims] ), Dim_date[WeekNo] = WEEKNUM ( TODAY (), 2 ) - 2 )
VAR _weeks1 =
    CALCULATE ( SUM ( [Claims] ), Dim_date[WeekNo] = WEEKNUM ( TODAY (), 2 ) - 1 )
VAR Avg10wclaims =
    DIVIDE (
        _weeks1 + _weeks2 + _weeks3 + _weeks4 + _weeks5 + _weeks6 + _weeks7 + _weeks8 + _weeks9 + _weeks10,
        10
    )
RETURN
    IF ( Avg10wclaims >= ClaimCount, "#D64550", "#089000" )

Best Regards,

Amy 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , for rolling week refer these

https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123

Hope you have used "field" option in conditional formatting?

https://radacad.com/dax-and-conditional-formatting-better-together-find-the-biggest-and-smallest-numbers-in-the-column
https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-values

[Anonymous]

Yes, i have used the field option.

[jdbuchanan71 · Accepted solution]

@Anonymous 

I messed with it a bit and your format measure should work.  I did do a different Avg 10w measure:

Avg 10w. claims = 
VAR _EOPW =
    CALCULATE ( MAX ( Dim_date[Date] ), Dim_date[WeekNo] < WEEKNUM ( TODAY (), 2 ) )
RETURN
    CALCULATE (
        AVERAGEX ( VALUES ( Dim_date[WeekNo] ), [ClaimCount] ),
        DATESBETWEEN ( Dim_date[Date], _EOPW - 69, _EOPW )
    )

 Take a look at my attached sample, see if that helps.  I used your color measure as is to format my ClaimCount field:

[Anonymous]

Thank you for your answer.

I have tried the solution, but i do not get the exact correct answer as before, and when im calculating the claims manually. Its a small difference, but not good enough.

[jdbuchanan71]

Rather than using the week number you can just adjust today back to the end of the prior week then calculate the avg using that date as a starting point.  That way having dates with the same week number won't matter.

Avg 10w. claims = 
VAR _EOPW =
    TODAY() - WEEKDAY(TODAY(),2)
RETURN
    CALCULATE (
        AVERAGEX ( VALUES ( Dim_date[WeekNo] ), [ClaimCount] ),
        DATESBETWEEN ( Dim_date[Date], _EOPW - 69, _EOPW )
    )

[Anonymous]

The calculated value is a little higher than the real value.

[jdbuchanan71]

You will need to share your .pbix, it works in my testing file so not sure what the problem is.

[Anonymous]

I still dont get the exact solution... I've tried different ways now..