cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GOWOD
Regular Visitor

Return Week number based on best week sales

Hi,

I have the following table of total sold by Week.

I am trying to extract just the Week of the best selling (max total sold from Year to selected date) so that I can put it onto a single value card.

I can get the value of the best total sales week with this formula

Total BW =
VAR year = SELECTEDVALUE('Date'[Year])
VAR Week = SELECTEDVALUE('Date'[YearWeek])
RETURN
MAXX( FILTER('MyTable', 'MyTable'[Year] = year && 'MyTable'[YearWeek] < Week),'MyTable)

I would appreciate it if somebody can help me to retrieve only the Week corresponding to the Best week sales.Thanks

 

Year YearWeek Week Total sold

20212021-34341
20212021-33331
20212021-31311
20212021-27273
20212021-26263
20212021-25253
20212021-242448
20212021-2323238
20212021-2222310
20212021-2121331
20212021-2020245
20212021-1919249
20212021-1818281
20212021-1717219
20212021-1616283
20212021-1515254
20212021-1414294
20212021-1313306
20212021-1212288
20212021-1111249
20212021-1010237
20212021-099283
20212021-088229
20212021-077308
20212021-066239
20212021-055300
20212021-044237
20212021-033206
20212021-022157
3 ACCEPTED SOLUTIONS
Nathaniel_C
Super User
Super User

Hi @GOWOD , try this:

Highest Week = 
var _maxSold= MAX(myTable[Total Sold]) // get the highest weekly sold

var _calc =CALCULATE(MAX(myTable[Week]),myTable[Total Sold]=_maxSold)

return _calc


Capturefian1.PNG

 


Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Ashish_Mathur
Super User
Super User

Hi,

Try these measures:

Total sales = SUM(Data[Total sold])
Best selling week = FIRSTNONBLANK ( TOPN ( 1, VALUES ( Data[Week] ), [Total Sales] ), 1 )

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

sktneer
Resolver I
Resolver I

Another way to achieve this...

 

Assuming the name of your table is Sales then try this...

 

Week with highest sale =
CONCATENATEX (
    TOPN (
        1,
        Sales,
        Sales[Total sold]
    ),
    Sales[Week],
    ", ",
    Sales[Total sold], DESC
)

View solution in original post

3 REPLIES 3
sktneer
Resolver I
Resolver I

Another way to achieve this...

 

Assuming the name of your table is Sales then try this...

 

Week with highest sale =
CONCATENATEX (
    TOPN (
        1,
        Sales,
        Sales[Total sold]
    ),
    Sales[Week],
    ", ",
    Sales[Total sold], DESC
)

View solution in original post

Ashish_Mathur
Super User
Super User

Hi,

Try these measures:

Total sales = SUM(Data[Total sold])
Best selling week = FIRSTNONBLANK ( TOPN ( 1, VALUES ( Data[Week] ), [Total Sales] ), 1 )

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Nathaniel_C
Super User
Super User

Hi @GOWOD , try this:

Highest Week = 
var _maxSold= MAX(myTable[Total Sold]) // get the highest weekly sold

var _calc =CALCULATE(MAX(myTable[Week]),myTable[Total Sold]=_maxSold)

return _calc


Capturefian1.PNG

 


Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.