Not able to filter the data based on dynamic where condition

[aatish178]

Hi Team,

I am new to power bi., here I have loaded a date field from the table, now I want to dynamically filter the date based on last one year date from today's date and the filter should be dynamic. For ex. Today is April 30,2024 so I want data from Apr 30,2023...

Tomorrow will be May 1, 2024 then after report refresh the data should be from May 1, 2023 and so on... I had tried filtering the date column in power query but it is asking to select a value from date field which is a hard-coded value.. I want a dynamic value based on today's date.

Can someone help with the possibile solution please @quantumudit 

Regards,

[dufoq3 · Accepted solution]

Hi, add this as a new step:

Replace Source with your previous step reference and [Date] with your [Column Name]

= Table.SelectRows(Source, each [Date] >= Date.AddYears(Date.From(DateTime.FixedLocalNow()), -1) )

Whole sample code:

let
    Source = Table.FromList(List.Dates(Date.AddDays(Date.From(DateTime.LocalNow()), -730), 1000, #duration(1,0,0,0)), Splitter.SplitByNothing(), type table[Date=date]),
    FilteredRows = Table.SelectRows(Source, each [Date] >= Date.AddYears(Date.From(DateTime.FixedLocalNow()), -1) )
in
    FilteredRows