Ranking values comparing two weeks

[robertosangi]

Hi, 

I have one question regarding the possibility to rank values based on a comparison between weeks.

Here the problem: I have this kind of table:

First of all i want to have first column as Wn and second column ad Wn-1, it is possible to do that? 
Thanks

Secondly I want to add two more columns. Each of them must rank (from 1 to n) the position of the highest value. In this example:
1. Palermo
2. Siracusa

....

Then, when I have the indexed value per each of the Wn and Wn-1 column I want to ad a sort of comparison between this index that shows if one unit performed better or worst respect the two last weeks.
In excel I cal use triangles up and down for indicate that. I want to buil somethink like this:

Thank you in advance

[johnt75]

Assuming that you have a date table, and assuming that you already have a measure which calculates PdL for the current week, then you can create additional measures as below

PdL Prev Week = CALCULATE( [PdL], DATEADD( 'Date'[Date], -7, DAY) )

Rank This Week = RANKX( 'Table', [PdL] )

Rank Last Week = RANKX( 'Table', [PdL Prev Week] )

Change in Rank = [Rank This Week] - [Rank Last Week]

Once you've added all these to a table visual you can use conditional formatting on the Change in Rank column to add icons

[robertosangi]

Hi @johnt75 

I didn't have a table for date. I have only a column from which I calculate weeks (WEEKNUM) as follow: 

looking at this condition, how should i proceed?

Thanks

[johnt75]

The safest option would be to create a date table and link that to the date column in your data table.

It would be possible to amend my code to work with a week number column, but it would be fiddly. Firstly you'd need to either change your current Week column so that it was only the week number, without the "Week " text, then you could change the measure to look at week - 1, but you'd run into problems when going over year boundaries when the week number resets. You'd also need to cater for situations where the week number could be 53, not 52.

Safer and easier to create a date table.

[robertosangi]

Perfect @johnt75 , i'll try. Which is the measure expression for calculating PdL for the current week?

Because actually this is the result: 

With the following measures:

PdLCurrentWeek = CALCULATE([PdL],DATEADD('Date'[Date],7,DAY))

PdLLastWeek = CALCULATE([PdL],DATEADD('Date'[Date],-7,DAY))

RankCurrentWeek = RANKX('Date',[PdLCurrentWeek])

RankLastWeek = RANKX('Date',[PdLLastWeek])

RankDifference = [RankCurrentWeek]-[RankLastWeek]

Thanks

[johnt75]

which measure is in the Wn column on that visual? that's the one you should use for as the base measure for calculating PdL for current week.

also, I think the RANKX functions need the data table as a parameter, not the Date table

[robertosangi]

This are the values @johnt75 :

Wn = PdLCurrentWeek

Wn-1 = PdlLastWeek

[johnt75]

You need the RANKX functions to work over the UT table, not the Date table

[robertosangi · Accepted solution]

Seems is not working anyway @johnt75 

Those are now the formulas but the results seems wrong because i filtered the current week table manually and results 433 count and not 2264 as per measure 

Could you please rewrite formulas as you expected? Thanks

[johnt75]

Is it possible to share your pbix file ?

[robertosangi]

@johnt75 unfortunatelly is not possible. Maybe could be possible to have a short meeting with screen sharing

[robertosangi]

I solved the measurement problem. Only rank doesn't Work... Here the situation:

[VIJAYKUMART]

IF(

HASONEVALUE(

Table name (unita)

),

RANKX(

ALL(

table name of unita (unita)

),

pdlcurrentweek measure

)

)

[robertosangi]

Hi @VIJAYKUMART, unfortunatelly doesn't work