topic Re: Rank and Rank Increment/Decrement from selected week vs previous week in DAX Commands and Tips

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

db1Intermtn — Wed, 22 Jul 2020 10:26:57 GMT

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.

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

amitchandak — Wed, 22 Jul 2020 10:58:43 GMT

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-Last-Week/ba-p/1051123

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/367415

For Icon

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

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

db1Intermtn — Thu, 23 Jul 2020 10:37:36 GMT

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

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

amitchandak — Thu, 23 Jul 2020 10:49:21 GMT

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

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

db1Intermtn — Thu, 23 Jul 2020 11:07:27 GMT

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.

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

sturlaws — Tue, 04 Aug 2020 14:48:12 GMT

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.