Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Conditional formatting when the measure used for formatting contains WEEKNUM

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.   ...
Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    6 years ago

    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:

     

v-xicai's avatar
v-xicai
Icon for Community Support rankCommunity Support
6 years ago

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.