Return Week number based on best week sales

[Anonymous]

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

2021	2021-34	34	1
2021	2021-33	33	1
2021	2021-31	31	1
2021	2021-27	27	3
2021	2021-26	26	3
2021	2021-25	25	3
2021	2021-24	24	48
2021	2021-23	23	238
2021	2021-22	22	310
2021	2021-21	21	331
2021	2021-20	20	245
2021	2021-19	19	249
2021	2021-18	18	281
2021	2021-17	17	219
2021	2021-16	16	283
2021	2021-15	15	254
2021	2021-14	14	294
2021	2021-13	13	306
2021	2021-12	12	288
2021	2021-11	11	249
2021	2021-10	10	237
2021	2021-09	9	283
2021	2021-08	8	229
2021	2021-07	7	308
2021	2021-06	6	239
2021	2021-05	5	300
2021	2021-04	4	237
2021	2021-03	3	206
2021	2021-02	2	157

[Nathaniel_C · Accepted solution]

Hi @Anonymous , 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

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

[Ashish_Mathur · Accepted solution]

Hi,

Try these measures:

Total sales = SUM(Data[Total sold])

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

[sktneer · Accepted solution]

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
)

[Ashish_Mathur · Accepted solution]

Hi,

Try these measures:

Total sales = SUM(Data[Total sold])

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

[Nathaniel_C · Accepted solution]

Hi @Anonymous , 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

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