PowerBI - Ability to Prompt User for Date retrieved table, Date becomes query variable

[dwglot]

Greeting, trying to figure out a way to prompt the PowerBI Report user to select a date from a table, and then the date selected becomes the query variable value.

Here is my PowerBI Query, need the FromDate and ToDate to be set by the User selecting;

= Sql.Database("Server", "Database", [Query="Declare
@FromDate as Date = '" & FromDate & "',
@ToDate as Date = '" & ToDate & "'

SELECT
[ID]
,[CompanyCode]
,[Report_Affiliate_Code]
,[NAV_Affiliate_Code_List]
,[NAV_BankAccount_List]
,[Description]
,dbo.fn_ClearedPaymentsCountBAI(@FromDate,@ToDate, [XXX_Affiliate_Code_List],[CompanyCode]) as PaymentCount
,dbo.fn_ClearedPaymentsAmountBAI(@FromDate,@ToDate, [XXX_Affiliate_Code_List],[CompanyCode]) as PaymentAmount
FROM [dbo].[AffiliateReporting]
WHERE dbo.fn_ClearedPaymentsCountBAI(@FromDate,@ToDate, [XXX_Affiliate_Code_List],[CompanyCode]) <> 0
ORDER BY [Sort Order]
OPTION (RECOMPILE)"])

Any assistance is greatly appreciated,

dwglot

[DataInsights]

@dwglot,

See the article below regarding dynamic M query parameters:

https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-dynamic-m-query-parameters 

[dwglot]

Thank you DataInsights,

    Not quite what is required, I have two Date parameters (FromDate and ToDate), which the User will select from. The date selected then becomes a value needed for a function within the query. Basically, placing the date values selected into the function variables.

let
selectFromDates = Type.Is(Value.Type(FromDateParameter), List.Type),
selectToDates = Type.Is(Value.Type(ToDateParameter), List.Type),

Query="
SELECT
[ID]
,[CompanyCode]
,[Report_Affiliate_Code]
,[NAV_Affiliate_Code_List]
,[NAV_BankAccount_List]
,[Description]
,dbo.fn_ClearedPaymentsCountBAI(("selectFromDates","selectToDates"),....

Any assistance is greatly appreciated.

Thank you,

dwglot

[DataInsights · Accepted solution]

@dwglot,

Here's an example using a custom SQL function. The data type of the parameters is Date, and is converted to text in M to make the concatenation work. Upon execution in SQL, the data type of the parameters is converted to Date.

let
    StartDate = Text.From(prmStartDate),
    EndDate = Text.From(prmEndDate),
    Source = Sql.Database("localhost", "Sandbox", [Query="select Sandbox.dbo.CountDaysInDateRange('" & StartDate & "', '" & EndDate & "')", CreateNavigationProperties=false])
in
    Source

[dwglot]

The solution did not work. While I am able to change the dates while editted the parameters, the users need to set the dates from the report view, acting similar that a slicer.

[DataInsights]

@dwglot,

It sounds as if you want to use dynamic M query parameters (I sent a link in my original reply). I was unable to adapt the solution to use dynamic M query parameters due to the query using Import mode (Import mode was automatically chosen with no chance to override it). If the query can be converted to DirectQuery, it should work. You would need to create a date table in your database and create two DirectQuery tables that point to the database date table. This would allow you to bind the parameters (data types must match).