CODEWITHSUNDEEP
sqlpowerbi
TheGeeky
TheGeeky

Reputation: 971

Compare between dates in power bi parameters

I have a query like this: 

select count(distinct userid) as SoftActiveUsers from bookingrequestticket where PickupTime > @start_time

and I made "start_time as a date type parameter" and the query is like this:

let
Source = Sql.Database("server", "database", [Query="select count(distinct userid) as SoftActiveUsers from bookingrequestticket where PickupTime > "&start_time&" "])
in
Source

but the result is:

We cannot apply operator & to types Text and Date.
Details:
    Operator=&
    Left=select count(distinct userid) as SoftActiveUsers from bookingrequestticket where PickupTime > 
    Right=1/16/2019

so how can i compare with two dates ?

Upvotes: 0

Views: 55

Answers (1)

Andrey Nikolov
Andrey Nikolov

Reputation: 13460

The & operator only works on text values. You'll need to use converter functions to get text values from your non-text column, or DateTime.ToText in this case. Format the date as YYYYMMDD to be sure it is recognized properly. Your M code should look like this:

let
    Source = Sql.Database("server", "database", [Query="select count(distinct userid) as SoftActiveUsers from bookingrequestticket where PickupTime > '" & DateTime.ToText(start_time, "yyyyMMdd") & "'"])
in
    Source

Upvotes: 1

Related Questions