Forum Discussion
Date Check in year to date last year
if Date.IsInYearToDate(Date.AddYears([Date], 2)) then "Yes"
else if Date.IsInYearToDate(Date.AddYears([Date], 1)) then "Yes"
else if Date.IsInYearToDate([Date]) then "Yes"
else "No"
Hello
I have a query that I need help improving. We have a date check that references a date we start 1/1/19 to current day. We use this date check to make sure that we don't keep anything that is more than 2 years ago and anything from after yesterday last year or 2 years ago from a sales report (We get this report daily) we use. Or that how it is supposed to be. the code currently brings in any sales last year or 2 years ago during the same day.
Ex what we have: todays date is 10/9/24 so in 2023 sales we are bringing in everything up to and including 10/9/24.
Ex what we want: todays date is 10/9/24 we want all sales up to and including yesterday 10/8/24. Same for 2022.
My thinking is to do date.currentlocaltime and do a dateadd.day -1 to that but I can't seem to figure it out. We still don't want any over 2 years ago so right now anything 12/31/21 and before.
Thanks!
Hi,
Thanks for the solutions rajasaadk_98 and PhilipTreacy offered, and i want to offer some more information for user to refer to.
hello Anonymous , you can create a custom column and input the following.
let _today=Date.AddDays(Date.From(DateTime.LocalNow()),-1), _twoyearsago=Date.AddYears(_today,-2), _oneyearsago=Date.AddYears(_today,-1), _startmonth=Date.StartOfYear(_today), _starttwoyears=Date.StartOfYear(_twoyearsago), _startoneyear=Date.StartOfYear(_oneyearsago) in if ([Date]>=_starttwoyears and [Date]<=_twoyearsago) or ([Date]>=_startoneyear and [Date]<=_oneyearsago) or ([Date]>=_startmonth and [Date]<=_today) then "Yes" else "No"e.g today is 10/16/2024
Ouput
Then filter it, it can work, and you can refer to the following whole code.
let Source = List.Dates(#date(2019,1,1),Duration.Days(Date.From(DateTime.LocalNow())-#date(2019,1,1)),#duration(1,0,0,0)), #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error), #"Added Custom1" = Table.AddColumn(#"Converted to Table", "Custom", each let _today=Date.AddDays(Date.From(DateTime.LocalNow()),-1), _twoyearsago=Date.AddYears(_today,-2), _oneyearsago=Date.AddYears(_today,-1), _startmonth=Date.StartOfYear(_today), _starttwoyears=Date.StartOfYear(_twoyearsago), _startoneyear=Date.StartOfYear(_oneyearsago) in if ([Date]>=_starttwoyears and [Date]<=_twoyearsago) or ([Date]>=_startoneyear and [Date]<=_oneyearsago) or ([Date]>=_startmonth and [Date]<=_today) then "Yes" else "No"), #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Custom] = "Yes")) in #"Filtered Rows"Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
- PhilipTreacy
Super User
Anonymous
You could just filter your table by selecting rows where the date is >= today -2 years
= Table.SelectRows(#"Previous Step Name", each [Date] >= Date.AddYears(Date.From(DateTime.LocalNow()), -2 ))Regards
Phil
- Omid_Motamedise
Nice solution
rajasaadk_98Helper I
To refine your logic and achieve the date filtering you want in Power Query M, here's an optimized approach based on your scenario. You need to:
- Ensure that you're excluding the current day and focusing on all records up to yesterday.
- Keep everything from up to two years ago, starting from the current day.
Modified Logic:
The key step is to adjust your date logic so that you capture dates up to yesterday, but also exclude anything older than 2 years ago.
Here's the modified Power Query M code:
let
// Get today's date (current local time)
Today = DateTime.LocalNow(),// Calculate yesterday's date
Yesterday = Date.AddDays(Date.From(Today), -1),// Check if the date is within the valid range: yesterday of last year or the year before
CheckDate = if [Date] >= Date.AddYears(Yesterday, -2) and [Date] <= Yesterday then "Yes" else "No"
in
CheckDateHi,
Thanks for the solutions rajasaadk_98 and PhilipTreacy offered, and i want to offer some more information for user to refer to.
hello Anonymous , you can create a custom column and input the following.
let _today=Date.AddDays(Date.From(DateTime.LocalNow()),-1), _twoyearsago=Date.AddYears(_today,-2), _oneyearsago=Date.AddYears(_today,-1), _startmonth=Date.StartOfYear(_today), _starttwoyears=Date.StartOfYear(_twoyearsago), _startoneyear=Date.StartOfYear(_oneyearsago) in if ([Date]>=_starttwoyears and [Date]<=_twoyearsago) or ([Date]>=_startoneyear and [Date]<=_oneyearsago) or ([Date]>=_startmonth and [Date]<=_today) then "Yes" else "No"e.g today is 10/16/2024
Ouput
Then filter it, it can work, and you can refer to the following whole code.
let Source = List.Dates(#date(2019,1,1),Duration.Days(Date.From(DateTime.LocalNow())-#date(2019,1,1)),#duration(1,0,0,0)), #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error), #"Added Custom1" = Table.AddColumn(#"Converted to Table", "Custom", each let _today=Date.AddDays(Date.From(DateTime.LocalNow()),-1), _twoyearsago=Date.AddYears(_today,-2), _oneyearsago=Date.AddYears(_today,-1), _startmonth=Date.StartOfYear(_today), _starttwoyears=Date.StartOfYear(_twoyearsago), _startoneyear=Date.StartOfYear(_oneyearsago) in if ([Date]>=_starttwoyears and [Date]<=_twoyearsago) or ([Date]>=_startoneyear and [Date]<=_oneyearsago) or ([Date]>=_startmonth and [Date]<=_today) then "Yes" else "No"), #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Custom] = "Yes")) in #"Filtered Rows"Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
