Forum Discussion
How to filter with multiple values in multiple columns
Hi edhans,
I want Jan 2014 - Sep 2017.
2014, 1-12
2015, 1-12
2016, 1-12
2017, 1-09
You should be able to use the M query below to filter the date column in this scenario. :smileyhappy:
FilterRows = Table.SelectRows(dbo_DimDate, each ([CalendarYear]*12+[MonthNumberOfYear]>=(2014*12+1))and(([CalendarYear]*12+[MonthNumberOfYear]<=(2017*12+9)))) in FilterRowsRegards
parry2k wrote:you should use between when filtering between 2014 and 2017 and similar to month
How would that look? I cannot even write that down on paper?
(between 2014 and 2017) and (between 1 and 9) wouldn't work
(between 2014 and 1) and (2017 and 9) wouldn't work either as I don't see how the logic would tell the system to get 2015 and 3
why this will not work:
(between 2014 and 2017) and (between 1 and 9) wouldn't work
parry2k wrote:why this will not work:
(between 2014 and 2017) and (between 1 and 9) wouldn't work
Because that will only give me months 1-9 in 2014, 2015, 2016, and 2017.
It will omit months 10-12 in 2014, 2015, and 2016.
I want Jan 2014 - Sep 2017.
2014, 1-12
2015, 1-12
2016, 1-12
2017, 1-09
Hi edhans,
I want Jan 2014 - Sep 2017.
2014, 1-12
2015, 1-12
2016, 1-12
2017, 1-09
You should be able to use the M query below to filter the date column in this scenario. :smileyhappy:
FilterRows = Table.SelectRows(dbo_DimDate, each ([CalendarYear]*12+[MonthNumberOfYear]>=(2014*12+1))and(([CalendarYear]*12+[MonthNumberOfYear]<=(2017*12+9)))) in FilterRowsRegards
This works, and was not entirely sure why until I worked through the logic of it.
So you are just counting months here. 2014 * 12 = 24,168 months since year zero, then adding the months.
Clever.
ok then you have to add custom date column created from year and month and then filter on date from Jan 1, 2014 to Sep 2017.
That is I guess the way to go.