Failing with RANK - how do you rank values in the scope of weeks?

[Anonymous]

Hi 

here's what's my data looks like - included image

As you can see, I'm able to use RANKX in the ALL scope, but I need to only rank the values in week scope.

in my image:

the value 87 should get the highest rank it its week, and not 791 that belongs to the week before.

Thank you

[parry2k]

@Anonymous i did the similar rank solution in another post, take a look.

[Anonymous]

Thank you but it only did half the job (maybe i wasn't specific enough).

Now it looks like this - which is perfect, but i want to show that max value for every single day of the week.

So, if the max value is 87 for week 15:

i want that value to be displayed (in a card visual) for every single day, no matter which day is picked from the Date Picker, can't seem to find the right formula..

[parry2k]

@Anonymous for max try following measures

Max in the week = 
MAXX( VALUES( Table[WeekNumber] ), [your measure] )

[Anonymous]

I tried that but it doesnt work, again it shows the value of the measure for each day.

[parry2k]

@Anonymous i think i'm missing what you are looking for? Is there any way you can share your working pbix file, remove any sensitive information.

[Anonymous]

Can't really, but here's a better view of my data.

The date 11/04/2019 is sliced but the "best in week" measure shows blank, even though there's the value 87 that is ranked 1 in that week.

Also, if I filter on the day before (10/04/2019) it shows the value 17 which ranked 3, and not the 1st rank of that week.

[parry2k]

@Anonymous this will work, In expression, change table and column name as per your data model.

Best Sales in Week = 
VAR __year = MAX( DimDate[CalendarYear] )
VAR __week = MAX( DimDate[WeekNumberOfYear] )
RETURN 
MAXX( FILTER( ALL( DimDate ), DimDate[WeekNumberOfYear] = __week && DimDate[CalendarYear] = __year ), [Sales] )