Forum Discussion
How to assign default values for selection parameters from a query
- 2 years ago
I have moved your post to the Power Query section of the forum, hopefully someone here will be more knowledgeable and can provide you with a more elegant solution 🙂
I have moved your post to the Power Query section of the forum, hopefully someone here will be more knowledgeable and can provide you with a more elegant solution 🙂
Hi dk_dk,
Thank you for your solution, it's really helpful.
It works as I thought, the codes I made as below.
..............
/* To define ym variables to get latest 6 months dynamically */
CurrDate = DateTime.Date(DateTime.LocalNow()),
prevmonth5 = Date.AddMonths(CurrDate,-5),
prevmonth4 = Date.AddMonths(CurrDate,-4),
prevmonth3 = Date.AddMonths(CurrDate,-3),
prevmonth2 = Date.AddMonths(CurrDate,-2),
prevmonth1 = Date.AddMonths(CurrDate,-1),
// Year yyyy
Y5 = Number.ToText(Date.Year(prevmonth5)),
Y4 = Number.ToText(Date.Year(prevmonth4)),
Y3 = Number.ToText(Date.Year(prevmonth3)),
Y2 = Number.ToText(Date.Year(prevmonth2)),
Y1 = Number.ToText(Date.Year(prevmonth1)),
Y0 = Number.ToText(Date.Year(CurrDate)),
// Month mm
M5 = Number.ToText(Date.Month(prevmonth5),"00"),
M4 = Number.ToText(Date.Month(prevmonth4),"00"),
M3 = Number.ToText(Date.Month(prevmonth3),"00"),
M2 = Number.ToText(Date.Month(prevmonth2),"00"),
M1 = Number.ToText(Date.Month(prevmonth1),"00"),
M0 = Number.ToText(Date.Month(CurrDate),"00"),
// YearMomth - yyyymm
YM5 = Text.Combine({"[0CALMONTH].[", Y5, M5, "]"}),
YM4 = Text.Combine({"[0CALMONTH].[", Y4, M4, "]"}),
YM3 = Text.Combine({"[0CALMONTH].[", Y3, M3, "]"}),
YM2 = Text.Combine({"[0CALMONTH].[", Y2, M2, "]"}),
YM1 = Text.Combine({"[0CALMONTH].[", Y1, M1, "]"}),
YM0 = Text.Combine({"[0CALMONTH].[", Y0, M0, "]"}),
#"QM_C03/ZQ_QM_C03_001X" = QM_C03{[Id="QM_C03/ZQ_QM_C03_001X"]}[Data],
#"Added Items" = Cube.Transform(#"QM_C03/ZQ_QM_C03_001X",
{
// {Cube.ApplyParameter, "[!V000001]", {{"[0CALMONTH].[202402]", "[0CALMONTH].[202403]", "[0CALMONTH].[202404]", "[0CALMONTH].[202405]", "[0CALMONTH].[202406]", " [0CALMONTH].[202407]"}}},
{Cube.ApplyParameter, "[!V000001]", {{YM5, YM4, YM3, YM2, YM1, YM0}}},
...........................