Dynamic parameter in Power Query

[Anonymous]

Dear all,

Please your help to advise for below issue.

I have a Power BI report get data from SAP BW. There is parameter for weekly version and horizon of week in data selection. 

It's fixed selection (Ex: [202049]) so I need to change to dynamic value base on the current date.

* Before:

*After add formula:

* Issue:

If I use the above formula then +1 to get value of the next week, then I have to list down all of week and there is issue with transition year. Please advise:

1. For horizon week, how can I create a range of horizon in short (from 202001 to 202052) instead of list down all weeks.

2. For transition of year: avoid issue with transition year Ex: invalid value of 202054

Appreciate your support, thanks so much!

*Here is what I added in the code:

#"Currentweek" = Number.ToText(Date.Year(DateTime.Date(DateTime.LocalNow()))) & Number.ToText(Date.WeekOfYear(DateTime.Date(DateTime.LocalNow()))),

Then I replace fixed value by calculation:

{Cube.ApplyParameter, "[YPOWKVER]", {"[YVER_WK].["&Currentweek&"]"}},
{Cube.ApplyParameter, "[!V000005]", {"[0CALWEEK].["&Currentweek&"]"}},

[Jimmy801]

Hello @Anonymous 

you have to calculate the yearweek with a custom function, because there is no integrated function of power query to do that. Here an example (you have probably to add a "0" in case the week-number has only one charachter

(dDate as date) =>
let
    Quelle = if
Number.RoundDown((Date.DayOfYear(dDate )-(Date.DayOfWeek(dDate , Day.Monday)+1)+10)/7)=0
 
then 
Number.ToText(Date.Year(dDate)-1)&Number.ToText(Number.RoundDown((Date.DayOfYear(#date(Date.Year(dDate )-1,12,31))-(Date.DayOfWeek(#date(Date.Year(dDate )-1,12,31), Day.Monday)+1)+10)/7))
 
else if
(Number.RoundDown((Date.DayOfYear(dDate )-(Date.DayOfWeek(dDate , Day.Monday)+1)+10)/7)=53
and (Date.DayOfWeek(#date(Date.Year(dDate ),12,31), Day.Monday)+1<4))
 
then
Number.ToText(Date.Year(dDate)+1)&"01"
 
else 
Number.ToText(Date.Year(dDate))&Number.ToText(Number.RoundDown((Date.DayOfYear(dDate)-(Date.DayOfWeek(dDate, Day.Monday)+1)+10)/7)) 
in
    Quelle

Copy paste this code to the advanced editor in a new blank query to see how the solution works. 

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Anonymous]

Hi Jimmy,

Thanks so much for your advice. I tried to create a new blank table to add above code, then I saw that if I input a date in the table, it invokes a value of week#. However, I do not know how to connect it with the parameter of SAP BW. Could you please help to support:

1. How can I add this code to replace the weekly version and weekly horizon in SAP BW?

2. For week# with one character (Ex:20201), how can I add "0" for these weeks.

Appreciate your support!

[Jimmy801 · Accepted solution]

Hello @Anonymous 

this code now creates the weeks with 2 characters

(dDate as date) =>
let
    Quelle = if
Number.RoundDown((Date.DayOfYear(dDate )-(Date.DayOfWeek(dDate , Day.Monday)+1)+10)/7)=0
 
then 
Number.ToText(Date.Year(dDate)-1)& Text.End("0" & Number.ToText(Number.RoundDown((Date.DayOfYear(#date(Date.Year(dDate )-1,12,31))-(Date.DayOfWeek(#date(Date.Year(dDate )-1,12,31), Day.Monday)+1)+10)/7)),2)
 
else if
(Number.RoundDown((Date.DayOfYear(dDate )-(Date.DayOfWeek(dDate , Day.Monday)+1)+10)/7)=53
and (Date.DayOfWeek(#date(Date.Year(dDate ),12,31), Day.Monday)+1<4))
 
then
Number.ToText(Date.Year(dDate)+1)&"01"
 
else 
Number.ToText(Date.Year(dDate))& Text.End("0"&Number.ToText(Number.RoundDown((Date.DayOfYear(dDate)-(Date.DayOfWeek(dDate, Day.Monday)+1)+10)/7)),2)
in
    Quelle

Copy paste this code into a new blank query and call it DateToYYYYWW

In orginal query now you want the current week in this format you can write the code like this

let
CurrentYYYYWW= DateToYYYYWW(Date.From(DateTime.LocalNow())),
NextWeek = DateToYYYYWW(Date.AddDays(Date.From(DateTime.LocalNow()),7))
in NextWeek

now you can feed your cube-function with the one or other variable

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Anonymous]

Dear Jimmy,

Sorry for late reply.

Thank you very much for your support.

Just one more thing, could you please advise if it's possible to create a range of weeks (example: 52 weeks from the current week) insteads of manually input week by week in this code?

Ex: Below code is for current week and next week, but we need current week, next week, next 2 weeks, next 3 weeks,... next 52 weeks.

Appreciate your support!

[Jimmy801]

Hello @Anonymous 

what I was showing you is a function to convert a date into the format you need. Depending what date you are passing you are getting another week-number. So you just need to call my function with all your needed weeks and then use them in your query as needed. Here some examples

let
    ThisWeek = MyFunction(Date.From(DateTime.FixedLocalNow())), 
    NextWeek = MyFunction(Date.From(Date.AddWeeks(DateTime.FixedLocalNow(),1)))
in
    {ThisWeek,NextWeek}

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Anonymous]

Hello Jimmy,

After some searching I found the solution, But I tried to get a list of 52 week starting from the current week but was not able to fix this. Can you help please ?

let
Table = let
ThisWeek = DateToYYYYWW(Date.From(DateTime.FixedLocalNow())),
NextWeek =DateToYYYYWW(Date.From(Date.AddWeeks(DateTime.FixedLocalNow(),10)))
in
{ThisWeek,NextWeek}

202218

202228

[Anonymous]

Hi Jimmy,

Thanks a lot for your support.

Just one more thing, could you please help to adjust the code for the same issue with month? I tried to adjust the code but still could not get the correct result.

Issue for month:

Jan 2020 => current code 20201 (5 characters)

Should be 202001 (6 characters)

Thanks so much!

[Jimmy801]

Hello 

@Anonymous 

could you please then accept the answer as solution and open a new topic, because this request has nothing to do with your original request

Many thanks

Jimmy

[Anonymous]

Thanks Jimmy,

I opened a new topic, please your help to support.

https://community.powerbi.com/t5/Power-Query/Dynamic-YYYYMM-in-Power-Query/m-p/1580716#M48831

Thank you!