Cubeset with multiple Period range

I'm not sure, but why do you need specific months in SortBy ? You may sort by Calendar.Month, it shall work. Better on YearMonth to sort on month index, not alphabetical month name.

You’re trying to use a CUBESET formula in Excel to dynamically calculate the rank of a particular brand in each region for a specific period, with a range of months (e.g., Oct-Dec), and you're encountering issues when attempting to use a dynamic period range in your formula.

You are correctly trying to use a set of months (October to December), but you're encountering errors when passing a range of months for sorting or selection purposes.

To use a dynamic period range in CUBESET, you can modify the formula by passing the range as an array of months rather than hardcoding the specific months into the formula. You can dynamically generate the range based on your timeline by using a dynamic calculation that refers to the Min and Max values for the months.

Here is a approach to resolving the issue:

You can use Excel functions to dynamically generate the range of dates. Here's how to modify your formula:

=CUBESET(
    "ThisWorkbookDataModel",
    "EXISTS([Brandmaster].[Brand].children, 
    ([State_Master].[State].[Maharashtra], 
    {[Calendar].[Month].[" & TEXT(MIN(DATEVALUE("Oct-2024")), "mmm-yyyy") & "]:[Calendar].[Month].[" & TEXT(MAX(DATEVALUE("Dec-2024")), "mmm-yyyy") & "]}))",
    "Set",
    2,
    "{([Measures].[S_Value],[State_Master].[State].[Maharashtra], 
    {[Calendar].[Month].[" & TEXT(MIN(DATEVALUE("Oct-2024")), "mmm-yyyy") & "]:[Calendar].[Month].[" & TEXT(MAX(DATEVALUE("Dec-2024")), "mmm-yyyy") & "]})}"
)

If you want to use the dynamic set in your sort or rank calculation, ensure that you pass the dynamic month range into the formula wherever the sorting happens. Make sure that the CUBESET is used to define the set, and the results are sorted accordingly.

=CUBERANKEDMEMBER(
    "ThisWorkbookDataModel",
    "CUBESET(
        "ThisWorkbookDataModel",
        "EXISTS([Brandmaster].[Brand].children, 
        ([State_Master].[State].[Maharashtra], 
        {[Calendar].[Month].[" & TEXT(MIN(DATEVALUE("Oct-2024")), "mmm-yyyy") & "]:[Calendar].[Month].[" & TEXT(MAX(DATEVALUE("Dec-2024")), "mmm-yyyy") & "]}))",
        "Set",
        2,
        "{([Measures].[S_Value],[State_Master].[State].[Maharashtra], 
        {[Calendar].[Month].[" & TEXT(MIN(DATEVALUE("Oct-2024")), "mmm-yyyy") & "]:[Calendar].[Month].[" & TEXT(MAX(DATEVALUE("Dec-2024")), "mmm-yyyy") & "]})}",
    1) 
)

If you want to make this more dynamic based on selected dates or user inputs (e.g., dynamic input for the range of months), you can use cell references for the start and end periods, like this:

=CUBESET(
    "ThisWorkbookDataModel",
    "EXISTS([Brandmaster].[Brand].children, 
    ([State_Master].[State].[Maharashtra], 
    {[Calendar].[Month].[" & TEXT(MIN(A1), "mmm-yyyy") & "]:[Calendar].[Month].[" & TEXT(MAX(A2), "mmm-yyyy") & "]}))",
    "Set",
    2,
    "{([Measures].[S_Value],[State_Master].[State].[Maharashtra], 
    {[Calendar].[Month].[" & TEXT(MIN(A1), "mmm-yyyy") & "]:[Calendar].[Month].[" & TEXT(MAX(A2), "mmm-yyyy") & "]})}"
)

Thoughts…

Using dynamic periods (e.g., Min/Max) in the CUBESET formula should be possible by utilizing date functions like TEXT, MIN, MAX, and cell references.

Be mindful of the exact date format that your model expects.

This solution will allow you to adapt to different periods dynamically, and you won't need to manually enter periods like "Oct-Dec" every time.

Please implement all these solutions with caution, as they are untested and taken from the AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and like it!

This will help all forum participants.