Forum Discussion

johnf's avatar
johnf
Icon for Helper I rankHelper I
10 years ago
Solved

Time calculation with DirectQuery

Hi,   Trying to figure out how to automatically filter a visualisation to show only rows that are up to 180 days from NOW() in DirectQuery   I have a column that has a date/time stamp but I only ...
Need Help
  • v-haibl-msft's avatar
    v-haibl-msft
    10 years ago

    johnf

     

    You can add a custom column with Power Query in query editor like below.

    let
    Source = Sql.Databases("HERBERTSQL2016"),
    #"Calculated Measure" = Source{[Name="Calculated Measure"]}[Data],
    #"dbo_Time calculation with DirectQuery" = #"Calculated Measure"{[Schema="dbo",Item="Time calculation with DirectQuery"]}[Data],
    #"Added Conditional Column" = Table.AddColumn(#"dbo_Time calculation with DirectQuery", "Custom", each if [Date] >= Date.AddDays(DateTime.Date(DateTime.LocalNow()), 180) then "N" else if [Date] < DateTime.Date(DateTime.LocalNow()) then "N" else "Y" )
in
#"Added Conditional Column"

     

    You can also use the SQL statement when connect the SQL DB.

    Select * From [Time calculation with DirectQuery] where [Time calculation with DirectQuery].Date >= GetDate() and [Time calculation with DirectQuery].Date < Dateadd(day, 180, GetDate())"]

     

    Best Regards,

    Herbert

johnf's avatar
johnf
Icon for Helper I rankHelper I
10 years ago

I've found one solution to this which is to create a column with the following calculation

 

Within 180 days = IF([Date]>=NOW()+180,"N",IF([Date]<NOW(),"N","Y"))

 

I can then use this to filter on. One thing that was confusing me in this was that in the editor when I entered the NOW() function it was being greyed out with a red squiggle line underneath which I was interpreting as meaning it wasn't valid in DirectQuery.

 

Apparently though, it works.

 

Interested if anyone has another solution.

v-haibl-msft's avatar
v-haibl-msft
Icon for Microsoft Employee rankMicrosoft Employee
10 years ago

johnf

 

You can add a custom column with Power Query in query editor like below.

let
    Source = Sql.Databases("HERBERTSQL2016"),
    #"Calculated Measure" = Source{[Name="Calculated Measure"]}[Data],
    #"dbo_Time calculation with DirectQuery" = #"Calculated Measure"{[Schema="dbo",Item="Time calculation with DirectQuery"]}[Data],
    #"Added Conditional Column" = Table.AddColumn(#"dbo_Time calculation with DirectQuery", "Custom", each if [Date] >= Date.AddDays(DateTime.Date(DateTime.LocalNow()), 180) then "N" else if [Date] < DateTime.Date(DateTime.LocalNow()) then "N" else "Y" )
in
#"Added Conditional Column"

 

You can also use the SQL statement when connect the SQL DB.

Select * From [Time calculation with DirectQuery] where [Time calculation with DirectQuery].Date >= GetDate() and [Time calculation with DirectQuery].Date < Dateadd(day, 180, GetDate())"]

 

Best Regards,

Herbert