Dataset date as parameter

[Anonymous]

Hi

Im trying to add a parameter for narrowing a a dataset.

= Sql.Database("xxxserver.database.windows.net", "DB", [Query="Declare @DateLoadFromMonthMinus1 as Date;
Declare @DateLoadFromYearMinus10 as Date;
set @DateLoadFromMonthMinus1=DATEADD(MONTH,-1,GETDATE());
set @DateLoadFromYearMinus10=DATEADD(YEAR,-10,GETDATE());
select * from [schema].[table] where THE_DATE > " & paramDateLoadFrom & ""])

The PowerBI parameter paramDateLoadFrom is text.

The reason why I try to do this is to reduce the imported dataset so its possible to soucecontrol the pbix file since we are using imported mode

Is this approch not valid? 

[v-deddai1-msft]

Hi @Anonymous ，

Would you please try the following m query:

= Sql.Database("xxxserver.database.windows.net", "DB", [Query="Declare @DateLoadFromMonthMinus1 as Date;
Declare @DateLoadFromYearMinus10 as Date;
set @DateLoadFromMonthMinus1= Date.AddMonths(DateTime.Date(DateTime.LocalNow()),-1);
set @DateLoadFromYearMinus10=Date.AddYears(DateTime.Date(DateTime.LocalNow()),-10);
select * from [schema].[table] where THE_DATE > " & paramDateLoadFrom & ""])

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[lbendlin]

how are you defining the parameter value? is it coming from the result of another query, or is it static?

[Anonymous]

I set the parameter on the dataset in the service to "@DateLoadFromYearMinus10". The scheduled refresh succeeds but its not using the expected parameter. Its seem like it using the other defined parameter from the example which is the default parameter