PowerBI: Filter by date

0
votes

I have a query, which I'd like to filter by the new date column I created.

Basically, anything which is 42 days earlier than that date is accepted.

I've tried doing a filter by date, and then substracting it by 42, but it does not work?

let
    #"SQL-JM" = let
    Source = Sql.Databases("xxx.xxx.xxx.xxx"),
    MNH = Source{[Name="DBT"]}[Data],
    #"DBO-JM" = DBT{[Schema="dbo",Item="DBO-JM"]}[Data]
in
    #"DBO-JM",
    #"Added Custom1" = Table.AddColumn(#"DBO-JM", "Start_of_QTR", each Date.StartOfQuarter(DateTime.LocalNow())),
in
    #"Filtered Rows"
2
powerbipowerquerym

2 Answers

2
votes

If I understand your task correctly, you don't need custom column at all.

I'd do like that:

let
    #"SQL-JM" = let
    Source = Sql.Databases("xxx.xxx.xxx.xxx"),
    MNH = Source{[Name="DBT"]}[Data],
    #"DBO-JM" = DBT{[Schema="dbo",Item="DBO-JM"]}[Data],
    GetFilterDate = Date.From(Date.StartOfQuarter(DateTime.LocalNow())), //You can use any logic to get that date
    FilterRows = Table.SelectRows(#"DBO-JM", each [DateStamp] < GetFilterDate) //You can also add some modifications to GetFilterDate using each row's values, if you need
in
    FilterRows
1
votes

Assume that you want to filter by the DateStamp column which 42 days earlier than Start_of_QTR is accepted, you can add the following line after the #"Added Custom1" line:

#"Filtered Rows" = Table.SelectRows(#"Added Custom1", each [DateStamp] < Date.AddDays([Start_of_QTR], -42))