Forum Discussion

RiniMaria's avatar
RiniMaria
Frequent Visitor
3 years ago
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  -

1. Always take the oldest 3 months

2. Always take the latest 3 months.

 

 

Need Help
Show and Tell
Tips and Tricks
  • BA_Pete's avatar
    BA_Pete
    3 years ago

     

    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

4 Replies

  • BA_Pete's avatar
    BA_Pete
    Super User
    3 years ago

    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

  • RiniMaria's avatar
    RiniMaria
    Frequent Visitor
    3 years ago

    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's avatar
      BA_Pete
      Super User
      3 years ago

       

      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