Rank and Rank Increment/Decrement from selected week vs previous week

[db1Intermtn]

I have a table visual with branch, client and revenue information. The revenue is coming from a measure which is affected by the week selector slicer. 

I need to show Rank for selected week and rank increment/decrement from selected week's rank vs previous week from selected week. In snapshot, Rank # and Rank are the requirements.

I tried to create rank with AllSelected function but it is always affected by week slicer and I cannot get previous week rank to compare and put Rank increment/decrement. DAX I tried for Rank

Ranking  = 
RANKX(
            ALLSELECTED(V_TopClients),
            CALCULATE(SUM(V_TopClients[Revenue])
            )
        )

I have v_TopClients that has weekly revenue information linking to dimCalendar, Date tables.

[amitchandak]

@db1Intermtn , Create this week and last week measure and create rank on them and then use that rank to compare movement

For week

https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...

https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/3...

For Icon

https://community.powerbi.com/t5/Desktop/FORMAT-icon-set-for-use-in-a-data-card/td-p/811692

[db1Intermtn]

 
@amitchandak 

I have calculated this week and previous week values with measures.

This Week = CALCULATE ( SUM ( V_TopClients[GrossProfit] ), ALLSELECTED ( DimCalendar[WeekCaption] ) )
Previous Week = CALCULATE ( SUM ( v_TopClients[GrossProfit] ), ALLSELECTED ( 'DimCalendar' ), DATEADD('Date'[Date],-7,DAY))

I have also calculated the Rank for this week with Dax:

Ranking This Week = RANKX( ALLSELECTED(V_TopClients), CALCULATE(SUM(V_TopClients[GrossProfit]) ) )

Now to show Rank increment/decrement based on the rank, I need previous week's rank. I am unable to get the rank for previous week. If I can get that, I was going to use the difference of this week's rank and previous week's rank to see the position change of the clients 

[amitchandak]

@db1Intermtn , share data from week table or week and year column 

[db1Intermtn]

@amitchandak 

Please find the sample data here:  https://1drv.ms/u/s!Ave_-9o8DQVEgRq42F7E2tvw1s9_?e=0CRLM4

I have uploaded for 2 weeks for top clients data and 7 weeks for sample calendar table I have.

My requirement is to show rank increase/decrease/constant for the clients on the list based on selected week vs previous week rank.

[sturlaws]

Hi @db1Intermtn 

write your measures like this:

this week =
VAR _maxWeekData =
    CALCULATE ( MAX ( TopClients[CalendarWeekKey] ), ALL ( TopClients ) )
RETURN
    CALCULATE (
        SUM ( TopClients[GrossProfit] ),
        FILTER ( ALL ( DimCalendar ), DimCalendar[CalendarWeekKey] = _maxWeekData )
    )

previous week =
VAR _maxWeekData =
    CALCULATE ( MAX ( TopClients[CalendarWeekKey] ), ALL ( TopClients ) )
VAR _previousWeek =
    CALCULATE (
        MAX ( TopClients[CalendarWeekKey] ),
        FILTER ( ALL ( TopClients ), TopClients[CalendarWeekKey] < _maxWeekData )
    )
RETURN
    CALCULATE (
        SUM ( TopClients[GrossProfit] ),
        FILTER ( ALL ( DimCalendar ), DimCalendar[CalendarWeekKey] = _previousWeek )
    )

Ranking This Week = RANKX( ALL(TopClients), [this week] )

Ranking previous Week = RANKX( ALL(TopClients), [previous week] )

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.