Power Query - rolling 3 months ahead, every month

[Cele]

Hi,
is there a formula in power query, that i can use to roll a month column 3 months ahead?

Say there is a table and there is a column that's called Renewal Month. And this column has dates shown as MM-DD-YYYY. How do i create a query step that every time i run it it does the following...

1. We are in August, so when i open it in the month of August. It filter that column so it shows everything in December.

2. Then in Sept when i open the file it filters and shows everything in January.

3. Then in Oct when i open the file it filters and shows everything in February.

4. Then in Nov when i open the file it filters and shows everything in March. and so on...

Please advise.

Cele

 

[Chihiro]

So... you have data for future dates?
Can you upload small sample data set? It can be either Data model as Excel Table.
Along with information on how that data is brought into the file (via Native Query, import entire table, etc).

Process to make dynamic filtering will depend on it.

Typically, I'd define variables in advanced editor and pass it to Native Query to limit the data brought in (heavy lifting done by SQL server). However, this method can't be used if you are importing entire table or importing from file etc.

 

[Cele]

see attached

 

[Chihiro]

So, regardless of the year, you want the Renewal month to be filtered on month, 3 month ahead of current?

Ex. Show November if it is currently in August.

Also, I assume this is not database native query, but straight import.

 

[Cele]

Its coming from another spreadsheet. And then the attachment will be a receiving that data from that other spreadsheet. And then i'll created the power query on that one attached above.

And, yes thats correct. What you said above. Say i open the spreadsheet on 9/25/17. I want the query to filter the dates and how only anything that has the month of January, regardless of year.

Cele

 

[Chihiro]

Ok, in that case. I'd do following.

1. Go in to Query Editor, then into Advanced Editor
2. Between let & Source line, add following line. This defines mFilter variable based on current datetime value and add 3 to month.

    mFilter = Date.Month(DateTime.Date(DateTime.LocalNow())) + 3,

3. Make sure your Renewal Month column is changed to date type (not datetime).
4. Add custom column named "FilterFlag" with following formula

=Date.Month([Renewal Month]) = mFilter

5. Filter to show only "True" in that column.
6. Remove the column and load data.

See attached sample.

 

[Cele]

See attached. When I pasted the area thats highlighted in yellow, below where it says in, i get an error. What do you think i am doing incorrectly?

 

[r2c2]

See attached. When I pasted the area thats highlighted in yellow, below where it says in, i get an error. What do you think i am doing incorrectly?

@Chihiro 's method should work, but when you copy paste the code, make sure the first step of the newly pasted M refers to previous step name (case sensitive).

Here is another way to get the result without opening Advanced Editor.

1. Load data in to PQ
2. Go to Add Column > Custom Column and create a column named "3rd month from now" with below formula
3. =Date.Month(DateTime.Date(DateTime.LocalNow()))+3
   
4. Select Renewal Month column, go to Add Column > Date > Month > Month. This will extract the month number from your Renewal Date in to a separate column.
5. Finally, insert a conditional column to check if Renewal Month = 3rd month from now, by using Add Column > Conditional column with below rule.
   
   
   
   
6. Filter "Show Hide" column to "Show"
7. Remove the three columns you added as you don't need them in extract.
8. Close and load.

 

[Cele]

r2c2, thanks but unfortunately, that method didnt work.

 

[Chihiro]

As @r2c2 wrote.

make sure the first step of the newly pasted M refers to previous step name (case sensitive).

So... first line of highlighted part becomes...

#"Added Custom" = Table.AddColumn(#"Changed Type to Renewal Month column", "FilterFlag", each Date.Month([Renewal Month]) = mFilter),

Also, his method works fine. Only difference being, he used 2 columns to compare against each other in row context, where as my method compares each row of Renewal Month against a variable.

FYI - When reporting back that one of provided solution does not work. It is good idea to post what error message you get at which line/step. And if no error message, what result was returned. That way, we can better see where the issue is, and help you fix it.

 

[Cele]

it only works if i "don't" do step 7.
Thanks r2c2. Much appreciated

 

[Chihiro]

it only works if i "don't" do step 7.

That is odd. Since, once each step is applied. Deleting columns subsequently does not negate previous operations completed. So in both r2c2's method and my method, we deleted unnecessary columns at the end of process, before loading table to worksheet.

But glad you got it working.