Dynamically find the top 3 monthly sales in last 12 months for each product / branch and then sum of those sales

[Gavin_FBu]

I've got a following table shows last 24 months monthly sales.  (Just pick 1 product and 1 branch for demo)

Rolling 12 Months Flag	Calendar Date	Branch ID	Product ID	sum([Financial Quantity Sold])
1	1/10/2023	201	10000101	4747
1	1/9/2023	201	10000101	1110
1	1/8/2023	201	10000101	2846
1	1/7/2023	201	10000101	1361
1	1/6/2023	201	10000101	2219
1	1/5/2023	201	10000101	2305
1	1/4/2023	201	10000101	1833
1	1/3/2023	201	10000101	2379
1	1/2/2023	201	10000101	2500
1	1/1/2023	201	10000101	3121
1	1/12/2022	201	10000101	2511
1	1/11/2022	201	10000101	3235
0	1/10/2022	201	10000101	3766
0	1/9/2022	201	10000101	3389
0	1/8/2022	201	10000101	2857
0	1/7/2022	201	10000101	3678
0	1/6/2022	201	10000101	1265
0	1/5/2022	201	10000101	1616
0	1/4/2022	201	10000101	4083
0	1/3/2022	201	10000101	3348
0	1/2/2022	201	10000101	3694
0	1/1/2022	201	10000101	2182
0	1/12/2021	201	10000101	529
0	1/11/2021	201	10000101	373
0	1/10/2021	201	10000101	200

Rolling 12 months Flag = 1 help to filter last 12 months only and I am trying to write a formula that sum up the top 3 monthly sales for each product by branch.  So I am looking to build:

Branch ID	Product ID	Top 3 Months sales
201	10000101	11103

In last 12 months for this product and branch, the highest 3 months sales are (4747 + 3235 + 3121) = 11103.
I try to use the following formula but not work:

RangeSum(
Aggr(
Above(
Sum({<[Rolling 12 Months Flag] = {"1"}>} [Financial Quantity Sold]),
0,
RowNo() <= 3
),
[Branch ID], [Product ID], [Calendar Date]
)
)

Anyone can help me out?

Thanks in advance.

Gavin

[ManuelRühl]

Maybe like this:

sum({<CalendarDate = {"=Rank(sum({<Rolling12MonthsFlag={1}>}FinancialQuantitySold))<4"}>} FinancialQuantitySold)

[Gavin_FBu]

Many Thanks.

I have a play with the logic and the formula only working when filter down to a branch for a product.  But if I do not put any filter and leave it as a flat table with all branches and products,the formula is not working.


Formula used:
sum({<[Calendar Date] = {"=Rank(sum({<[Rolling 12 Months Flag]={1}>}[Financial Quantity Sold]),4)<4"}>} [Financial Quantity Sold])
P.S: added mode 4 in Rank() as I only want the top 3 numbers of Qty Sold. 

An example here when I've only filtered product: 1353262 only and eyeball on branch: 146.
Left-hand table are using the formula that gives me 7918.  However, if you look at the right hand-side table, the rank() do flag correctly for the highest top 3 month's sale and they should sum up to 11103.

If I add another filter at a specific branch, the formula do give me the right number~

Any thought? 
Tried:  sum(aggr(sum({<[Calendar Date] = {"=Rank(sum({<[Rolling 12 Months Flag]={1}>}[Financial Quantity Sold]),4)<4"}>} [Financial Quantity Sold]),[Branch ID]))
return me the same result.

Cheers

Gavin