cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
yousseftb76
Frequent Visitor

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

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

1 ACCEPTED SOLUTION
mahoneypat
Microsoft
Microsoft

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

2 REPLIES 2
mahoneypat
Microsoft
Microsoft

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


works correctly.

 

Many many tanks

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors