Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

select rows from same period previous month

 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...
Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    9 years ago

    Anonymous,

    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

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Anonymous,

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi,

     

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

     

    Thank you,

    Narendra.