cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
Highlighted
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
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 384 members 3,895 guests
Please welcome our newest community members: