Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

How to assign default values for selection parameters from a query

Hi Experts,     I'd like to have power BI to refresh report data from a query based on a predefined criteria ,     e.g, get the latest six months' data from a data source (query), with a scheduled ...
Need Help
  • dk_dk's avatar
    dk_dk
    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 🙂

dk_dk's avatar
dk_dk
Super User
2 years ago

Hi Anonymous 

I am not a power query expert, but hopefully this solution works for you. I am sure someone can make the M code a bit more elegant:

My sample query with hardcoded values

let
    Source = Table.FromRows(
    {
        {202407},
        {202406},
        {202405},
        {202404},
        {202403},
        {202402}
    })

in
    Source

This just creates a table that contains the last 6 months in the format you want, hardcoded.

The modified query to return the same result dynamically:

let
    refTime = DateTime.Date(DateTime.LocalNow()),
    prevmonth5 = Date.AddMonths(refTime,-5),
    prevmonth4 = Date.AddMonths(refTime,-4),
    prevmonth3 = Date.AddMonths(refTime,-3),
    prevmonth2 = Date.AddMonths(refTime,-2),
    prevmonth1 = Date.AddMonths(refTime,-1),




    Source = Table.FromRows(
    {
        {Date.ToText(refTime,"yyyyMM")},
        {Date.ToText(prevmonth1,"yyyyMM")},
        {Date.ToText(prevmonth2,"yyyyMM")},
        {Date.ToText(prevmonth3,"yyyyMM")},
        {Date.ToText(prevmonth4,"yyyyMM")},
        {Date.ToText(prevmonth5,"yyyyMM")}
    })
in
    Source


I am not sure exactly how, but there should be a way to incorporate these variables (the refTime and prevmonths) in your query, in place of the hardcoded values. The query will calculate the current time with every refresh, so the months should change dynamically.


Hope this helps!

  • dk_dk's avatar
    dk_dk
    Super User
    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 🙂

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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}}},

      ...........................