Using Query Editor to limit sales flowing into PBI report

Power Query
1

Hi

Every day the sales in my report are refreshed. However, I only want the sales for the past two years from yesterday to filter from Power Query into my report.

I’m manually filtering by sales dates at the moment.

Is there any M Code I can use so that this process is automated?

For example, this morning the sales query was refreshed to include yesterday’s sales. A filter then checks all the sales dates and only includes the sales for the past two years up to yesterday. The filtered sales then feed through to the PBI report.

Thanks

2

So you need a dynamic way to generate that date value for your filter?
See this example:

image
image873×160 7.23 KB

To create the depicted record just copy this code into a new blank query
More Date M functions can be found here

let
    Source = [ Start of year 2 yrs ago = Date.AddYears( Date.StartOfYear( Date.From( DateTime.FixedLocalNow())), -2 ),  Yesterday 2 yrs ago = Date.AddYears( Date.AddDays( Date.From( DateTime.FixedLocalNow()), -1 ), -2 ) ]
in
    Source

I hope this is helpful.

3

Hi Melissa

Thank you! I’ve set up the query to obtain the dynamic minimum date for my sales:

image

How can I use this value to filter out any sales lower than that date value in Power Query or do I need to create a measure in Power BI using the dynamic date value?

image
image416×527 58 KB

4

Hi @KieftyKids,

That record was just for illustrating two options.

As for how to implement, just use the UI to create that filter step early on in your query and sub in the manually selected (random) date with the M code from the record provided earlier - the part after the equal sign you’ve selected to use.

I hope this is helpful

5

Hi @Melissa

Thanks. Now I understand.

Code added and working as hoped.