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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Create Dynamic parameter to load data in Power BI

Hi,

 

I am very new to Power BI. I have one requirement to load the data for one field from previous year to next 3 years.

So, the filed is 'PO_Plan_Receipt_Year' and it has all years starting from 2011 to 2025.

I want to load data in Power Bi only form previous year to next 3 years. so it should be always :

year(Today())-1 to 'year(Today())+3 i.e (2017 to 2021). I am expacting something like below image.

How I can achieve that?

Thanks!

 

Regards,

PoonamCapture.PNG

1 ACCEPTED SOLUTION

Hi @Anonymous

Please pay attention to the bold character, paste the following code in your Advanced editor.

let
Source = Sql.Database("10.76.62.207", "sagedb"),
dbo_V_OPPORTUNITIES_OIF = Source{[Schema="dbo",Item="V_OPPORTUNITIES_OIF"]}[Data],
#"Replaced Value" = Table.ReplaceValue(dbo_V_OPPORTUNITIES_OIF,"","0.0",Replacer.ReplaceValue,{"OIF_Value_EUR"}),
#"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"OIF_Value_EUR", type number}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Opp_Key", each [Opportunity_ID]&"-"&Date.ToText([Load_Creation_Date])),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"PO_Plan_Receipt _Year", Int64.Type}}),
Startdate=Date.Year(DateTime.LocalNow())-1,
Enddate=Date.Year(DateTime.LocalNow())+3,
#"Filtered Rows" = Table.SelectRows(#"Changed Type1", each [PO_Plan_Receipt _Year] >= Startdate and [PO_Plan_Receipt _Year] <= Enddate)
in
#"Filtered Rows

 

Best Regards

Maggie

View solution in original post

7 REPLIES 7
v-juanli-msft
Community Support
Community Support

Hi @Anonymous

Write this code in the Advanced editor.

let
    Source =******,
    #"Changed Type"=****,
    Startdate=Date.Year(DateTime.LocalNow())-1,
    Enddate=Date.Year(DateTime.LocalNow())+3,
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [date] >= Startdate and [date] <= Enddate)
in
    #"Filtered Rows"

11.png

 

Best Regards

Maggie

 

 

Anonymous
Not applicable

Hi @v-juanli-msft,

 

Thanks for your reply. I tried the above expression but I am getting below error. Can you please help me on that?

Capture.PNG

 

Regards,

Poonam

Hi @Anonymous

Could you check the data type of the "date" column? (or the column used before and after the "=<")

In my test, it is of Type Number.

Or could you share the code you used in the Advanced editor so i can analyze where is wrong?

Additionally, how about delete the operator, then re-write it? It seems you use < instead of <=.

 

Best Regards

Maggie

Anonymous
Not applicable

Hi @v-juanli-msft,

 

Thanks for your reply!

 

I am using below code :

 

let
Source = Sql.Database("10.76.62.207", "sagedb"),
dbo_V_OPPORTUNITIES_OIF = Source{[Schema="dbo",Item="V_OPPORTUNITIES_OIF"]}[Data],
#"Replaced Value" = Table.ReplaceValue(dbo_V_OPPORTUNITIES_OIF,"","0.0",Replacer.ReplaceValue,{"OIF_Value_EUR"}),
#"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"OIF_Value_EUR", type number}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Opp_Key", each [Opportunity_ID]&"-"&Date.ToText([Load_Creation_Date])),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"PO_Plan_Receipt _Year", Int64.Type}}),
Startdate=Date.Year(DateTime.LocalNow())-1,
Enddate=Date.Year(DateTime.LocalNow())+3,
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each [PO_Plan_Receipt _Year] >= Startdate and [PO_Plan_Receipt _Year] <= Enddate)
in
#"Filtered Rows

 

The data type of 'StartDate' is 'Any'. The data type of 'PO_Plan_Receipt _Year' was Text. I converted it to Whole number. (You can see the 'Change Type1' script code. I tried using "&StartDate&" but still it is not working. If I use hardcoded value "2017" , it works.

Could you pleas ehelp me on that?

Thanks!

 

Regards,

Poonam

Hi @Anonymous

Please pay attention to the bold character, paste the following code in your Advanced editor.

let
Source = Sql.Database("10.76.62.207", "sagedb"),
dbo_V_OPPORTUNITIES_OIF = Source{[Schema="dbo",Item="V_OPPORTUNITIES_OIF"]}[Data],
#"Replaced Value" = Table.ReplaceValue(dbo_V_OPPORTUNITIES_OIF,"","0.0",Replacer.ReplaceValue,{"OIF_Value_EUR"}),
#"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"OIF_Value_EUR", type number}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Opp_Key", each [Opportunity_ID]&"-"&Date.ToText([Load_Creation_Date])),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"PO_Plan_Receipt _Year", Int64.Type}}),
Startdate=Date.Year(DateTime.LocalNow())-1,
Enddate=Date.Year(DateTime.LocalNow())+3,
#"Filtered Rows" = Table.SelectRows(#"Changed Type1", each [PO_Plan_Receipt _Year] >= Startdate and [PO_Plan_Receipt _Year] <= Enddate)
in
#"Filtered Rows

 

Best Regards

Maggie

Is there a way to automatically change the data source in PBI daily via an equation (for example: add 8340 to the middle number in the link every day. So day 1 would by 8340, day to would automatically change to 16680, etc)?

 

Thank you! 

Anonymous
Not applicable

Hi @v-juanli-msft,

 

Thank you so much for your reply. It is working as expected 🙂

 

Regards,

Poonam

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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