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 want to show rows that are within 180 days of when the report is viewed.

 

Can anyone assist?

 

Thanks,

John

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

2 Replies

  • 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