Annualize current year Sales and rank top gainers and losers against last year

[mbuhary]

I need to RANKX in my database based on Sales this year vs Last year top Gainers and Losers.

But since my current fiscal year is still incomplete I would like to annualize my current yeat numers before comparing for Ranking.

Also I would like to Rank only my VIP Customers so I need to filter in that condition as well.

[StoreCode] [StoreName] [Date] [Year] [CustomerID] [CustomerName] [VIP_Yes/No] [Sales]

Any help is appreciated

[CheenuSing]

Hi @mbuhary

Please upload some sample data and the output desired to googledrive or onedrive and share the link to find a solution.

Cheers

CheenuSing

[mbuhary]

Here is the link for the file in Google Drivehttps://drive.google.com/file/d/10ewMtLNjbKnJnvU6TJlTZ9gyMeKnUFE6/view?usp=sharing

[CheenuSing]

Hi @mbuhary

Got your data set.  Need the follwing clarifications.

a)  Is the year based on calendar year Jan - Dec or different ?

b) What is the logic for generating the annualised sales for remaining months of current year ?

c) Can you do a manual computation of the result expected from the dataset shared and send it .

You could send me a private message instead of replying here to avoid too many conversations until a solution is found.

Cheers

Cheenusing

[mbuhary]

a) yes the year is Jan to Dec

b) current year sales divided by (max date - 31st Dec Last year) Times 365

c) based on the current available data if we apply the above calculation to Year on Year Growth the Ranks should be as follows:

A Bnk Rank 1 (Growth 128%)

IC BK U Rank 2 (Growth 56%)

 Similarly we have to rank if there are any negative number as well, ranking the lowest as Rank 1 most loser and so on.