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

1
votes

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. enter image description here

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

  This Week GP = 

var _Total = 
    CALCULATE(
            SUM(V_TopClients[revenue]),
           DATESBETWEEN('Date'[date],[current_week_start_date], [current_week_end_date])
        
    )

    return _Total


Previous Week GP = 

var _Total = 
    CALCULATE(
            SUM(V_TopClients[revenue]),
           DATESBETWEEN('Date'[date],[previous_week_start_date], [previous_week_end_date])
        
    )

    return _Total

Rank This Week = 
    RANKX(
                ALLSELECTED(V_TopClients),
                CALCULATE(SUM(V_TopClients[Revenue])
                )
            )

--Update: added dax for measures

Here I am able to get the rank for this week i.e. the week selected on slicer. But unable to get rank for previous week.

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

2
powerbidax
Are you showing weekly branch wise top client? – mkRabbani
@mkRabbani Yep, data shown weekly ( based on week slicer ) for branch wise Top Clients i.e. top clients based on Revenue. – Success Maharjan
What branch wise top clients means? you are keeping the 1 single client for each branch based on revenue? – mkRabbani
Basically my report shows top 100 clients based on their revenue for the selected week. The branch is just another additional information on the table visual. – Success Maharjan

2 Answers

1
votes

Here is another and probably the best option for you-

Step-1: Create a new custom table based on your table "TopClient". The code is as below-

group_by_result_new = 
VAR group_wise_revenue = 
GROUPBY (  
    TopClients,
    TopClients[CalendarWeekKey],
    TopClients[clientID],
    "gp_this_week", SUMX(CURRENTGROUP(), TopClients[GrossProfit])
) 

RETURN
SELECTCOLUMNS (
    group_wise_revenue,
    "CalendarWeekKey", TopClients[CalendarWeekKey],
    "clientID", TopClients[clientID],
    "gp_this_week", [gp_this_week]
)

Step-2: Create relation between table "DimCalendar" and "group_by_result_new" using the column "CalendarWeekKey"

Step-3: Create a new column (remember it a column) as below-

gp_prev_week = 
VAR client_id = group_by_result_new[clientID]
VAR calendar_key_this_week = group_by_result_new[CalendarWeekKey]

VAR end_date_this_week = 
LOOKUPVALUE(
    DimCalendar[WeekEndingDate],
    DimCalendar[CalendarWeekKey], CONVERT(calendar_key_this_week,INTEGER)
)

VAR end_date_prev_week = CONVERT(end_date_this_week,DATETIME) - 7 

VAR calendar_key_prev_week =
LOOKUPVALUE(
    DimCalendar[CalendarWeekKey],
    DimCalendar[WeekEndingDate] , end_date_prev_week
)

VAR gp_prev_week = 
LOOKUPVALUE(
    group_by_result_new[gp_this_week],
    group_by_result_new[CalendarWeekKey],calendar_key_prev_week,
    group_by_result_new[clientID], CONVERT(client_id,INTEGER)
)

RETURN gp_prev_week

Step-4: Create a new column (remember it a column) for RANK this week as below-

rank_this_week = 
RANKX (
    FILTER (
        group_by_result_new,
        group_by_result_new[CalendarWeekKey] = EARLIER (group_by_result_new[CalendarWeekKey])
    ),
    group_by_result_new[gp_this_week],
    ,
    DESC
    // ,
    // DENSE
)

Step-5: Create a new column (remember it a column) for RANK prev week as below-

rank_prev_week = 
RANKX (
    FILTER (
        group_by_result_new,
        group_by_result_new[CalendarWeekKey] = EARLIER (group_by_result_new[CalendarWeekKey])
    ),
    group_by_result_new[gp_prev_week],
    ,
    DESC
    // ,
    // DENSE
)

And that's all! This should also work same as my previous solution. Cheers!!

1
votes

In DAX, there are builtin function PREVIOUSMONTH, PREVIOUSQUARTER and PREVIOUSYEAR available. But as you are searching for weekly data comparison, you required your own date periods to calculate. I just can give you some idea as below-

First, crate 4 measure based on your slicer week/date selection.

Example:

current_week_end_date = SELECTEDVALUE(Dates[Date])
current_week_start_date = SELECTEDVALUE(Dates[Date]) - 7

previous_week_end_date = SELECTEDVALUE(Dates[Date]) - 8
previous_week_start_date = SELECTEDVALUE(Dates[Date]) - 15

Now, you need 2 separate measure to calculate this week and previous week total revenue. Example Measures are given below-

1.

this_week_revenue = 
CALCULATE(
    SUM(table[revenue]),
    DATESBETWEEN(
        'Dates'[Date],
        [current_week_start_date],
        [current_week_end_date]
    )
)

2.

previous_week_revenue = 
CALCULATE(
    SUM(table[revenue]),
    DATESBETWEEN(
        'Dates'[Date],
        [previous_week_start_date],
        [previous_week_end_date]
    )
)

Now you have both weekly value in your hand and you can compare measure "this_week_revenue " with measure "previous_week_revenue" to generate the directions indicators.

Hope this will help!

Below image is just for reference:

Just for ref: