cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Syndicate_Admin
Administrator
Administrator

power query: current year to date versus previous year for the same period

Hello

I have a 2 year sales table (previous year to this day).

Example: today is 22/01/2022

I want to filter in power Query the column "FECHA_VENTA" in such a way to get only the sales from:

- from January 1 of the Current Year to Diadehoy/mesactual/current year (that is, from 01/01/2022 to 22/01/2022))

- and the same period but last year:

- from January 1 of the YearAnterior to Diadehoy/mesactual/añoaPrevious (that is, from 01/01/2021 to 22/01/2021)

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

1 ACCEPTED SOLUTION

I already have the solution, it has been given to me by the user "mahoneypat" of the Forum in English, it works perfectly for those who needed it:

Here is the configuration of the date filter in power Query:

= 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))

View solution in original post

3 REPLIES 3
lbendlin
Super User
Super User

Are you sure you want to do that in Power Query?

Please provide sanitized sample data that fully covers your issue. Paste the data into a table in your post or use one of the file services. Please show the expected outcome.

Initial data:

FECHA_VENTA Invoice
01/01/2021 1
02/01/2021 2
17/01/2021 17
18/01/2021 18
20/01/2021 20
21/01/2021 21
22/01/2021 22
23/01/2021 23
24/01/2021 24
28/01/2021 28
29/01/2021 29
30/01/2021 30
31/01/2021 31

....

....

....

....

....

31/12/2021 3258
01/01/2022 32
02/01/2022 33
03/01/2022 34
04/01/2022 35
05/01/2022 36
06/01/2022 37
07/01/2022 38
12/01/2022 43
13/01/2022 44
14/01/2022 45
19/01/2022 50
20/01/2022 51
21/01/2022 52
22/01/2022 53

Data desired result assuming that we are on 22/01/2022:

FECHA_VENTA Invoice
01/01/2021 1
02/01/2021 2
17/01/2021 17
18/01/2021 18
20/01/2021 20
21/01/2021 21
22/01/2021 22
01/01/2022 32
02/01/2022 33
03/01/2022 34
04/01/2022 35
05/01/2022 36
06/01/2022 37
07/01/2022 38
12/01/2022 43
13/01/2022 44
14/01/2022 45
19/01/2022 50
20/01/2022 51
21/01/2022 52
22/01/2022 53

I'm looking to complete the filter conditions in column FECHA_VENTA in Power Query, I guess 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)) .................................

Nb:

in MS 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()

I already have the solution, it has been given to me by the user "mahoneypat" of the Forum in English, it works perfectly for those who needed it:

Here is the configuration of the date filter in power Query:

= 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))

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

Power BI Dev Camp Session 22 without aka link and time 768x460.jpg

Check it Out!

Watch Session 22 Ted's Dev Camp along with past sessions!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!

Top Kudoed Authors