Date Filter

[RiniMaria]

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  -

1. Always take the oldest 3 months

2. Always take the latest 3 months.

[RiniMaria]

Thank you so much! This works!

[RiniMaria]

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!

[BA_Pete · Accepted solution]

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

[BA_Pete]

Hi @RiniMaria ,

Can you provide a small example of what your data looks like please?

The main thing I need to see is the date format and frequency in your table i.e. are they all individual dates, month-end dates, or something else.

Pete