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:

     

jdbuchanan71's avatar
jdbuchanan71
Icon for Super User rankSuper User
6 years ago

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's avatar
Anonymous
Not applicable
6 years ago

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