Forum Discussion

robertosangi's avatar
robertosangi
Helper I
4 years ago
Solved

Ranking values comparing two weeks

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

    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's avatar
johnt75
Super User
4 years ago

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's avatar
    robertosangi
    Helper I
    4 years ago

    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's avatar
      johnt75
      Super User
      4 years ago

      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's avatar
        robertosangi
        Helper I
        4 years ago

        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