Forum Discussion
Date Filter
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
Hi BA_Pete,
My date column is in Date format (YYYY-MM-DD). Data is connected to different regions so the dates will be repeated and I have 1000+ rows of data in here. Here is a snippet -
Not sure if this answers you question. Let me know!
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