Date Check in year to date last year

[Anonymous]

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!

[Anonymous · Accepted solution]

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.

[rajasaadk_98]

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:

1. Ensure that you're excluding the current day and focusing on all records up to yesterday.
2. 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
CheckDate

[PhilipTreacy]

@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