cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
jengwt Member
Member

"Last X Calendar Years" filter for Dynamics API pull

I have a report that pulls a HUGE set of data from Dynamics API. In order to conrtol the size of this data, I would like to limit the data pulls to the past year and this year. This is not to be hard-coded, but rather dynamic so that as the years change so will the data pull.

 

If you were to code this in SQL, it would look like this: 

WHERE DATE >= '1-1-' || (YEAR(CURRENT DATE) - 1)

 

The closest thing that I've found is to use the filtering functionality in the query editor, but you can only use "Last X Years", "Last X Months", etc. I need to be able to specify Calender Year, as you would with the relative date slicer.

 

I was think it may be codeable. Take the following Filtered Rows query step:

= Table.SelectRows(#"Removed Columns"
, each Date.IsInPreviousNYears([createdon], 2)
)

 

Unfortunately, it's not as simple as changing: 

= Table.SelectRows(#"Removed Columns"
, each Date.IsInPreviousNCalendarYears([createdon], 2)
)

 

Does anyone know any solutions? Thanks!

1 REPLY 1
v-cherch-msft Super Contributor
Super Contributor

Re: "Last X Calendar Years" filter for Dynamics API pull

Hi @jengwt

 

Here is the reference for you.

https://excel.city/2015/09/relative-dates-and-periods-in-power-query/

 

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 112 members 1,480 guests
Please welcome our newest community members: