Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
Anonymous
Not applicable

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 job in power BI service.

    In case above, we need to select month values specifically to import data from a query 

    to form the dataset for working on reports and dashboards in power desktop,

    then publish them to power BI service cloud.

    The problem is that selecting the last six month values and

    publishing periodically seems not make any sense, not really a good idea.

    Therefore, is there any smart way to have this job done automatically ?

 

    In the "Advanced Editor" under query editor in power BI desktop, I can see the month values there like below

.....

    {Cube.ApplyParameter, "[!V000001]", {{"[0CALMONTH].[202402]", "[0CALMONTH].[202403]", "[0CALMONTH].[202404]", "[0CALMONTH].[202405]", "[0CALMONTH].[202406]", "[0CALMONTH].[202407]"}}},

   ....

     My idea is to have those values determined dynamically, e.g, those values would becomes [202403], [202404]....[202408]

     when the scheduled job runs in August. 

     Not sure whether it is possible or how to make the code changes to the codes in  "Advanced Editor"

     to make such a dynamic value determination happen.

 

    Any advice will be greatly apprecated.

 

     

    

 

1 ACCEPTED 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 🙂




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





View solution in original post

3 REPLIES 3
dk_dk
Super User
Super User

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!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





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 🙂




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Anonymous
Not applicable

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

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

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.