Cubeset with multiple Period range?

[kalpesh07]

Hey All! need help with creating a Cubeset formula! I've market data at region level for multiple period & brand! I want to know the rank of particular Brand in each region on Specific Period. Formula is working for a particular period but not working on a range! How can I use a range/dynamic period in cubeset.

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

This is working for particular month but when I want to get data for 3 month like Oct-Dec

=CUBESET(
    "ThisWorkbookDataModel",
    "EXISTS([Brandmaster].[Brand].children, 
    ([State_Master].[State].[Maharashtra], 
    {[Calendar].[Month].[Oct-2024]:[Calendar].[Month].[Dec-2024]}))",
    "Set",
    2,
    "{([Measures].[S_Value],[State_Master].[State].[Maharashtra],{[Calendar].[Month].[Oct-2024]:[Calendar].[Month].[dec-2024]})}"
)

I'm getting error on this. Range is working on set expression but while using same in Sort By (Highlighted Part), I'm getting the error.

One way around is passing Each Month separately not tried that but I want it to be dynamic using timeline & using Min/Max function to define the range, it could be 2month or 12 month.

Pls help me with this.

Thanks

[v-menakakota]

Hi @kalpesh07 ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-menakakota]

Hi @kalpesh07 ,

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.

Thank you.

[v-menakakota]

Hi @kalpesh07 ,

Thank you for helpful response @bhanu_gautam ,I wanted to check if you had the opportunity to review the information provided by bhanu_gautam . Please feel free to contact us if you have any further questions. If this answers your question, please Accept it as a solution so other members can easily find it.

Regards,
Menaka.

[bhanu_gautam · Accepted solution]

@kalpesh07 , Try using

=CUBESET(
"ThisWorkbookDataModel",
"EXISTS([Brandmaster].[Brand].children,
([State_Master].[State].[Maharashtra],
{[Calendar].[Month].[Oct-2024]:[Calendar].[Month].[Dec-2024]}))",
"Set",
2,
"AGGREGATE({([Measures].[S_Value],[State_Master].[State].[Maharashtra],{[Calendar].[Month].[Oct-2024]:[Calendar].[Month].[Dec-2024]})}, SUM)"
)

[kalpesh07]

Getting #N/A error. It is working fine till part one of the formula but after using Aggregate it is showing the error.

[v-menakakota]

Hi @kalpesh07 ,

In this forum you will find people who are good at Fabric and Power BI if you believe your query can be resolved by people who are good at Excel and who have experience using powerquery within Excel, you can utilize Excel community: Welcome to the Excel Community | Microsoft Community Hub 

Thanks and Regards