Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello everyone!
I have a date column that I would like to filter on all dates 7 Days before today.
Is there any chance to determine that with Power Query?
Solved! Go to Solution.
Hi @joshua1990 ,
You could try below M code
let
Source = List.Dates(#date(2020,4,12), 30,#duration(1,0,0,0)),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each if [Column1]<=DateTime.Date(DateTime.LocalNow())-#duration(7,0,0,0) then 1 else 0),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = 1))
in
#"Filtered Rows"
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @joshua1990 ,
You could try below M code
let
Source = List.Dates(#date(2020,4,12), 30,#duration(1,0,0,0)),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each if [Column1]<=DateTime.Date(DateTime.LocalNow())-#duration(7,0,0,0) then 1 else 0),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = 1))
in
#"Filtered Rows"
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Yes, it is DateTime.LocalNow I believe. https://www.powerquery.io/date-time/datetime.localnow
Hi @joshua1990
You can do it in the Model using Slicer or Filter
https://docs.microsoft.com/en-us/power-bi/visuals/desktop-slicer-filter-date-range
Or you can use Relative Date Dimension
https://community.powerbi.com/t5/Community-Blog/Relative-Date-Dimension/ba-p/779039
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.