Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
aatish178
Helper III
Helper III

Not able to filter the data based on dynamic where condition

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,

 

1 ACCEPTED SOLUTION
dufoq3
Super User
Super User

Hi, add this as a new step:

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

 

dufoq3_0-1714462465338.png

 

= 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

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

View solution in original post

1 REPLY 1
dufoq3
Super User
Super User

Hi, add this as a new step:

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

 

dufoq3_0-1714462465338.png

 

= 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

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors