Dynamic Date Filter in Power BI

[Anonymous]

I am trying to display data that occured before the past year. For example, I need to see all the people who have not had this test done in the past year. Right now I have a filter where I manually enter today's date a year ago (so 7/11/2021) to show the data before this date. How do I make a dynamic filter that updates with each day to show all the data from the past year? Thanks!

[v-jingzhang]

Hi @Anonymous 

Currently this is not available for filters in the report layer. If you only want to have data that occurred before the past year in the data model, you can try @Vijay_A_Verma 's solution which will only import data that occurred before the past year into your report. 

However, if you also want to have data that occurred after that date in your report, there is no good workarounds at present. You need to filter dates in DAX formulas to make calculation based on data occurred before the past year. But this cannot be applied to page or report filters. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Vijay_A_Verma]

You will need to use this argument for your filter where you are inputting manual date

Date.AddYears(Date.From(DateTime.FixedLocalNow()),-1)

See an example of working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tcy7DYAwDAXAXVxHMs8QPiV/drCy/xoYGylprzhVWilRZrB0WKgkpc1gchA47AGNHCFS5Qzpq1wmc9vcBuD8gTg8BgNj/KW8", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, #"Last Test Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Last Test Date", type date}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Last Test Date]<=Date.AddYears(Date.From(DateTime.FixedLocalNow()),-1)))
in
    #"Filtered Rows"