Forum Discussion
Solved
Date Filter
Hello, I have a table which has information always for the last 12months (rolling months). This table is refreshed every month. I need to be able to filter this table two ways in Power Query - ...
That's perfect, thanks.
I'd add a relative month column to your data in Power Query, something like this:
RelativeMonth = ( Date.Year([Time]) * 12 + Date.Month([Time]) ) - ( Date.Year(DateTime.LocalNow()) * 12 + Date.Month(DateTime.LocalNow()) )This should give you a column that contains the values -1 to -12 which can then be easily used in either M or DAX to grab any month you want. For example:
// EarliestThreeMonths_M List.Contains({-12, -11, -10}, [RelativeMonth]) // EarliestThreeMonths_DAX YourTable[RelativeMonth] IN {-12, -11, -10} // LatestThreeMonths_M_DAX [RelativeMonth] >= -3 // MiddleFourMonths_M List.Contains({-5, -6, -7, -8}, [RelativeMonth]) // or [RelativeMonth] >= -8 and [RelativeMonth] <= -5 // and so on...Pete
Thank you so much! This works!