select rows from same period previous month

[narendra1]

 Hi All,

i want help in pulling rows from table which have date column, in the date column it has values from both 2016 and 2017.

now i want to pull particular records which fall in YTD and same period in 2016.

i am working in power bi query editor.

from there i want to pull values only upto 01-09-2016.

Any help most appreciable.

Thanks in advance.

[v-yuezhe-msft]

@narendra1,

Do you want to only select values between January 2016 and September 2016 in Query Editor? If that is the case, you can use custom filter of the date column like the following sample.


However, if you want to select values between January 2016 and September 2016 along with these values that between January 2017 and September 2017, you can filter the rows as shown in the sample below.


If the above steps don't help, please describe more details about your requirement, and share the sample data of your table that can be copied and pasted, I will test it in my Power BI Desktop.

Regards,
Lydia

[narendra1]

Hi Lydia,

I highly appriciate your reply and that will work as well.

but my requirement is dynamic, that means for this month "jan 2016 to sep 2016" and "jan 2017 to sep 2017".  when calender moved to october the filter should automatically fetch values "jan 2016 to oct 2016" and "jan 2017 to oct 2017"

hope this make sense.

Thanks in advance

[v-yuezhe-msft · Accepted solution]

@narendra1,

You can add the following code in the Advanced Editor of your query.

#"Filtered Rows" = Table.SelectRows(#"Changed Type1", each Date.Year([DateColumn]) = Date.Year(DateTime.LocalNow())-1 and Date.Month([DateColumn]) <= Date.Month(DateTime.LocalNow()) or Date.Year([DateColumn]) = Date.Year(DateTime.LocalNow()) and Date.Month([DateColumn]) <= Date.Month(DateTime.LocalNow()))

You can review the following sample  to get mode details.

Regards,
Lydia

[narendra1]

Hi,

Thank you very much, it seems it is working for me.

Thank you,

Narendra.