Dynamic parameter for calendar with data source from SAP BW

[Anonymous]

Dear all,

May I get your help on below issue.

I create a report with data source from SAP BW that I have to select parameter of calendar (month) when getting data to the report. It is the fixed selection and I have to adjust the parameter after a period.
In case I want to get dynamic parameter (Ex: last month + current month) then I do not have to update the parameter anymore.

Below is the code of parameter:

{Cube.ApplyParameter, "[YCALMONT]", {{"[YORDMONTH].[202010]", "[YORDMONTH].[202011]", "[YORDMONTH].[202012]"}}},

Appreciate your advice.

Thank you!

[Steels_Cat]

@v-alq-msft . Will be great if you provide the full solution of this topic. I have similar case.

[v-alq-msft]

Hi, @Anonymous 

You may create two queries with the following codes to get lastmonth and current month and then refer to these two queries in other query. The pbix file is attached in the end.

Query1:

let
    Source = let 
d = DateTime.Date( DateTime.LocalNow())
in
Date.Year(d)*100+Date.Month(d)
in
    Source

 

Query2:

let
    Source = let 
d = DateTime.Date( DateTime.LocalNow()),
dlastmonth = Date.AddMonths(d,-1)
in
Date.Year(dlastmonth)*100+Date.Month(dlastmonth)
in
    Source

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[zadullabja]

Hi Allan, Thank you for your post. Really helped me. But how do i apply this to my query? 

{Cube.ApplyParameter, "[YCMPERB]", {{"[0CALMONTH].[202301]"}}},

[Ajay346]

Did you find any solution to it