Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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.
Solved! Go to 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 🙂
Proud to be a 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!
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 🙂
Proud to be a Super User! | |
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}}},
...........................
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
16 | |
9 | |
8 | |
7 | |
7 |