Forum Discussion
Conditional formatting when the measure used for formatting contains WEEKNUM
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:
Super UserAnonymous
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:
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 ) )I still dont get the exact solution... I've tried different ways now..
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.