Cubeset Sort By with date range

[Magic_Sponge]

I use a CUBESET formula combined with CUBERANKEDMEMBER to generate a list of, say, brands with several filters which users can select from cells in the excel worksheet. One of these in the CUBESET is a date range. I also want to use this date range in the sort_by argument part of the CUBESET function. I have simplified it a bit here, but this works just fine when the dates in cells F5 and G5 are the same but not when they are different.

CUBESET("ThisWorkbookDataModel","NONEMPTY([tblBrands].[Brand].Children,([Measures].[Actual 9Ltrs],[tblCustomer].[CustomerName].["&$B$5&"],[tblDates].[Mth].["&TEXT($F$5,"dd/mm/yyyy")&"]:[tblDates].[Mth].["&TEXT($G$5,"dd/mm/yyyy")&"]))","Brand",2,"{([Measures].[Actual 9Ltrs],[tblDates].[Mth].["&TEXT(F5,"dd/mm/yyyy")&"],[tblCustomer].[CustomerName].["&$B$5&"])}")

The date range in the set_expression part works just fine, but I can't get the sort_by to adhere to the date range. I've played around with the formula a lot but can't get it to work, it just returns #N/A. Just to be clear again, if I have the same date in F5 and G5 I get exactly what I need. If F5 and G5 are different the CUBESET works if I don't have any date range in the sort_by part of the formula (in other works the date range works fine in the set_expression part).

Any ideas from anyone? Maybe it just can't be done but it seems odd that it can't! Thank you in advance.

 

[RoryA]

It's not really clear to me what you are actually trying to sort by here. Wouldn't it just be "[Measures].[Actual 9Ltrs]"?

 

[Magic_Sponge]

It's not really clear to me what you are actually trying to sort by here. Wouldn't it just be "[Measures].[Actual 9Ltrs]"?

If I just add that to the sort by expression, it will sort the list of brands based on how much they have purchased over the entire data set in the model. What I'm looking to do is sort it highest to lowest based on what they've purchased in that specified date range only.

There must well be a much better way to do this, I've cobbled together what little I know from googling and boards like this so I might be approaching it in the wrong way!