Forum Discussion

BMltc's avatar
BMltc
Icon for Helper II rankHelper II
4 years ago
Solved

RANKX on a specific date

Hi Community,

 

I know that this question has been asked many time but I can't find my answer. I have a list of sales by product, by location and by date. 

DateProductLocationSales
20/12/2021AppleParis5
20/12/2021AppleToronto6
20/12/2021AppleNew York56
20/12/2021BananaParis62
20/12/2021BananaToronto100
20/12/2021BananaNew York24
20/12/2021OrangeParis22
20/12/2021OrangeToronto66
20/12/2021OrangeNew York73
21/12/2021AppleParis99
21/12/2021AppleToronto51
21/12/2021AppleNew York28
21/12/2021BananaParis6
21/12/2021BananaToronto30
21/12/2021BananaNew York86
21/12/2021OrangeParis77
21/12/2021OrangeToronto32
21/12/2021OrangeNew York65
22/12/2021AppleParis31
22/12/2021AppleToronto7
22/12/2021AppleNew York94
22/12/2021BananaParis50
22/12/2021BananaToronto22
22/12/2021BananaNew York16
22/12/2021OrangeParis65
22/12/2021OrangeToronto29
22/12/2021OrangeNew York55

 

I want to display a table with my 3 products, being able to get the average of sales for the last date, the date before, ranking them on these specific date and finally compare the rankings. 

 Sales on 21/12/21RankSales on 22/12/21RankCompare
Apple59,3144,021
Banana40,7329,330
Orange58,0249,71-1

 

I can't succeed in my rankx function.

 

Can you please help me?

 

Many thanks all

Need Help
  • MFelix's avatar
    MFelix
    4 years ago

    Hi BMltc ,

     

    The you just need to add a calculation to precede your measures to check if it's the maximum date:

     

    Average value = 
var MaximumDate = CALCULATE(MAX('Table'[Date]),ALL('Table'[Date]))
Return
IF(SELECTEDVALUE('Table'[Date]) = MaximumDate,
    CALCULATE ( AVERAGE ( 'Table'[Sales] ) )
)

Average value (previous) = 
var MaximumDate = CALCULATE(MAX('Table'[Date]),ALL('Table'[Date]))
Return
IF(SELECTEDVALUE('Table'[Date]) = MaximumDate,
    CALCULATE (
        AVERAGE('Table'[Sales]),
        FILTER ( ALL ( 'Table'[Date] ), 'Table'[Date] = MAX ( 'Table'[Date] ) - 1 )
    )
)

Ranking = 
IF([Average value] <> BLANK(),
    RANKX (
        FILTER (
            SUMMARIZE ( ALLSELECTED ( 'Table' ), 'Table'[Date], 'Table'[Product] ),
            'Table'[Date] = MAX ( 'Table'[Date] )
        ),
        CALCULATE ( 'Table'[Average value] )
    )
)

Previous Ranking = 
IF(
    'Table'[Average value] <> Blank(),
    RANKX (
        FILTER (
            SUMMARIZE ( ALLSELECTED ( 'Table' ), 'Table'[Date], 'Table'[Product] ),
            'Table'[Date] = MAX ( 'Table'[Date] )
        ),
        CALCULATE ( 'Table'[Average value (previous)] )
    )
)

     

     

6 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    4 years ago

    Hi BMltc ,

     

    Don't know if you want to show the two  values but you can do the following:

    • Add a calendar table to your model
    • Create the following measures:
    Average value =
IF (
    SELECTEDVALUE ( 'Table'[Date] ) = MAX ( 'Calendar'[Date] ),
    CALCULATE ( AVERAGE ( 'Table'[Sales] ) )
)


Average value (previous) =
IF (
    SELECTEDVALUE ( 'Table'[Date] ) = MAX ( 'Calendar'[Date] ),
    CALCULATE (
        AVERAGE ( 'Table'[Sales] ),
        FILTER ( ALL ( 'Table'[Date] ), 'Table'[Date] = MAX ( 'Calendar'[Date] ) - 1 )
    )
)


Ranking =
IF (
    'Table'[Average value] <> BLANK (),
    RANKX (
        FILTER (
            SUMMARIZE ( ALLSELECTED ( 'Table' ), 'Table'[Date], 'Table'[Product] ),
            'Table'[Date] = MAX ( 'Calendar'[Date] )
        ),
        CALCULATE ( 'Table'[Average value] )
    )
)


Previous Ranking =
IF (
    'Table'[Average value] <> BLANK (),
    RANKX (
        FILTER (
            SUMMARIZE ( ALLSELECTED ( 'Table' ), 'Table'[Date], 'Table'[Product] ),
            'Table'[Date] = MAX ( 'Calendar'[Date] )
        ),
        CALCULATE ( 'Table'[Average value (previous)] )
    )
)


Ranking Difference = [Ranking] - [Previous Ranking]

     

    Final result below:

    PBIX file attach.

     

    • BMltc's avatar
      BMltc
      Icon for Helper II rankHelper II
      4 years ago

      thank you MFelix , that is almost perfect! I have noticed that you have filtered the table

      is it possible to have the same table without this filter?

      the concept is that date will change and i want the latest date and compare the data a week ago.

       

      • MFelix's avatar
        MFelix
        Icon for Super User rankSuper User
        4 years ago

        Hi BMltc ,

         

        If you don't want  to filter the table, then you don't need the calendar table, redo the measures to:

         

        Average value = 
    CALCULATE ( AVERAGE ( 'Table'[Sales] ) )



Average value (previous) = 
    CALCULATE (
        [Average value],
        FILTER ( ALL ( 'Table'[Date] ), 'Table'[Date] = MAX ( 'Table'[Date] ) - 1 )
    )


Ranking = 
    RANKX (
        FILTER (
            SUMMARIZE ( ALLSELECTED ( 'Table' ), 'Table'[Date], 'Table'[Product] ),
            'Table'[Date] = MAX ( 'Table'[Date] )
        ),
        CALCULATE ( 'Table'[Average value] )
    )



Previous Ranking = 
    RANKX (
        FILTER (
            SUMMARIZE ( ALLSELECTED ( 'Table' ), 'Table'[Date], 'Table'[Product] ),
            'Table'[Date] = MAX ( 'Table'[Date] )
        ),
        CALCULATE ( 'Table'[Average value (previous)] )
    )


Ranking Difference = [Ranking] - [Previous Ranking]