RANKX on a specific date

[BMltc]

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. 

Date	Product	Location	Sales
20/12/2021	Apple	Paris	5
20/12/2021	Apple	Toronto	6
20/12/2021	Apple	New York	56
20/12/2021	Banana	Paris	62
20/12/2021	Banana	Toronto	100
20/12/2021	Banana	New York	24
20/12/2021	Orange	Paris	22
20/12/2021	Orange	Toronto	66
20/12/2021	Orange	New York	73
21/12/2021	Apple	Paris	99
21/12/2021	Apple	Toronto	51
21/12/2021	Apple	New York	28
21/12/2021	Banana	Paris	6
21/12/2021	Banana	Toronto	30
21/12/2021	Banana	New York	86
21/12/2021	Orange	Paris	77
21/12/2021	Orange	Toronto	32
21/12/2021	Orange	New York	65
22/12/2021	Apple	Paris	31
22/12/2021	Apple	Toronto	7
22/12/2021	Apple	New York	94
22/12/2021	Banana	Paris	50
22/12/2021	Banana	Toronto	22
22/12/2021	Banana	New York	16
22/12/2021	Orange	Paris	65
22/12/2021	Orange	Toronto	29
22/12/2021	Orange	New York	55

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/21	Rank	Sales on 22/12/21	Rank	Compare
Apple	59,3	1	44,0	2	1
Banana	40,7	3	29,3	3	0
Orange	58,0	2	49,7	1	-1

I can't succeed in my rankx function.

Can you please help me?

Many thanks all

[MFelix]

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]

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]

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]

[BMltc]

Thanks again @MFelix, sorry but my last request was not clear. In this example, my dataset is from 20/12 to 22/12 but the day after, it will be updated from 23/12 to 20/12 (and the day after 24/12 to 20/12). And I want the table you have done 

with the lastest date compared to 1 day before. is it clear?

Thanks again

[MFelix · Accepted solution]

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)] )
    )
)

[BMltc]

Really helpful, many thanks