Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Dynamic Date Filter in Power BI

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!

Need Help

2 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    4 years ago

    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"

     

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    4 years ago

    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.