current year to date vs. prior year for the same period

[yousseftb76]

Hello,

I have a sales table of 2 years (previous year until today).

Example: today is 01/22/2022 (01 january 2022)

I want to filter in power Query the column "DATE_SALE" in such a way to obtain only the sales since:

- from January 1 of the Current Year to Today's Day/current month/current year (that is, from 01/01/2022 to 01/22/2022))

- and the same period but last year:

- from January 1 of the Previous Year to Today's Day/current month/Previous Year (that is, from 01/01/2021 to 01/22/2021)

I am looking to complete the filter conditions in the DATE_SALE column in Power Query, I suppose it will be something like this:

= Table.SelectRows(#"columna", each [FECHA_VENTA] >= #datetime(Date.Year(DateTime.LocalNow())-1, 1, 1, 0, 0, 0)) and [Date] <= #datetime(Date.Year(DateTime.LocalNow())-1, Date.Month(DateTime.LocalNow()), 1, 0, 0, 0))..................................................

Can you please help me to correctly put these conditions in the date filter

in ACCESS it would be something like this:
Between DateSerial(Year(Date())-1;1;1) AND DateSerial(Year(Date())-1;Month(Date());Day(Date()))
AND
Between DateSerial(Year(Date());1;1) AND Today()

[mahoneypat · Accepted solution]

Please try a filter step with this expression. You can just add a filter step "between" and pick two dates, and then modify it in the Formula Bar to match the below.

= Table.SelectRows(#"Inserted Year", each let today = Date.From(DateTime.LocalNow()) in ([Date] >= Date.StartOfYear(Date.AddYears(today, -1)) and [Date] <= Date.AddYears(today, -1)) or (Date.Year([Date]) = Date.Year(today) and [Date] <= today))

Pat

[yousseftb76]

works correctly.

Many many tanks