Time calculation with DirectQuery

[johnf]

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

[johnf]

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 · Accepted solution]

@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