Filtering a cubeset function by date

[cmgish]

I have an Excel model with a single table for which I need to create a cubeset. I have a [Date] column and a [Team] column with many other columns of statistics. What I want to do is create a cubeset based on the date. My table is named Team_Stats, and looks like this:

I was hoping to be able to use the cubeset function to create one cubeset based on the date "11/4/2024", and another cubeset based on the date "11/6/2024". I have tried using the FILTER function nested within the CUBESET function, but I'm sure I'm getting the syntax wrong. Can anyone help? The goal of creating the cubesets is to use the CUBERANKEDMEMBER function to pull the top 3 teams or top 5 teams of any one statistic based on the date. Thanks to anyone that can help!
Chris

 

[RoryA]

You could use something like this (assumes a date in A2):

=LET(dt,TEXT(A2,"yyyy-mm-dd"),CUBESET("ThisWorkbookDataModel","([Team_Stats].[Date].&["&dt&"T00:00:00],[Team_Stats].[Team].children)","Cubeset for "&dt))

 

[cmgish]

Thanks so much RoryA! That formula did filter my cubeset the way I wanted it. However, for some reason I can't get my CUBERANKEDMEMBER function to work. Not really sure what I'm doing wrong. I copied the function you wrote and then added the parameters to sort the CUBESET in descending order, and then to sort by [Team_Stats].[FG%]. So now, the formula looks like this:

=LET(dt,TEXT(A2,"yyyy-mm-dd"),CUBESET("ThisWorkbookDataModel","([Team_Stats].[Date].&["&dt&"T00:00:00],[Team_Stats].[Team].children)","Cubeset for "&dt,2,"[Team_Stats].[FG%]"))

I then write my CUBEMEMBER function as:

=CUBEMEMBER("ThisWorkbookDataModel","[Team_Stats].[TRB]","Rebounds")

Now, my CUBERANKEDMEMBER function looks like this:

=CUBERANKEDMEMBER("ThisWorkbookDataModel",$Z$656,$AB658)

Z656 holds the CUBESET and AB658 holds the CUBEMEMBER function above. I do get a list of team names (which is what I'm going for), but the results are incorrect. It lists the team names in alphabetical order, so the cubeset is sorted by the team name rather than by the statistic I want

 

[RoryA]

I don't follow - why are you passing a cube member as the third argument to CUBERANKMEMBER? It should be a rank number. If you are trying to sort the results by different statistics, it doesn't make sense to sort the original cube set. You could simply get the set for the date:

=LET(dt,TEXT(A2,"yyyy-mm-dd"),CUBESET("ThisWorkbookDataModel","([Team_Stats].[Date].&["&dt&"T00:00:00],[Team_Stats].[Team].children)","Cubeset for "&dt)

then adjust that to sort by whatever metric you want:

=CUBESET("ThisWorkbookDataModel",Z656,2,"[Team_Stats].[FG%]")

or

=CUBESET("ThisWorkbookDataModel",Z656,2,"[Team_Stats].[TRB]")

and then use CUBERANKEDMEMBER on whichever of those sets you want.