Filter Date Column based on second Date column

[joshua1990]

I have a table with 2 date columns:

Date1	Date2
01.01.2024	05.01.2024
01.01.2024	09.01.2024

Here I would like to filter the second column to the next 15 months based on the Date1 columns.

How would you do that in Power Query?

[jennratten · Accepted solution]

Hello @joshua1990,

Your message doesn't say if you are trying to retain records in Date2 is greater than or less than 15 months from Date1 so I have given an example below which can solve for both. I have also used dates that can demonstrate the expected result.

In the screenshot below I have added a column which calculates the number of months between Date2 and Date1.  This creates the ability for you to then see the difference and filter based on your use case.  If seeing the difference as a separate column is not preferred then you can either remove the column or use the same logic in your filter statement.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJW0lEygjBNlGJ1UMSNcYibwMVjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date1 = _t, Date2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date1", type date}, {"Date2", type date}}),
    date_diff = Table.AddColumn(
                // starting table
            #"Changed Type",
            // name of new column to be created
            "MonthsBtwnDate2AndDate1", 
            each 
                // declare inline variables
                let 
                    // calculate the compelete years elapsed
                    DateDiffInYears = 
                        Date.Year ( [Date2] ) - Date.Year ( [Date1] ),
                    // calculate the remaining months
                    MonthsRemaining = 
                        Date.Month ( [Date2] ) - Date.Month ( [Date1] ),
                    // calculate the total number of months
                    DateDiffInMonths = 
                        ( DateDiffInYears * 12 ) + MonthsRemaining
                in
                    DateDiffInMonths,
                    Int64.Type
        )
in
    date_diff

  If you want to filter Date2 based on Date1 then it would be like this (retaining rows where Date2 is less than 15 months from Date1).

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJW0lEygjBNlGJ1UMSNcYibwMVjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date1 = _t, Date2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date1", type date}, {"Date2", type date}}),
    SelectRowsLessThan15Months = Table.SelectRows (
                // starting table
            #"Changed Type",
            each 
                // declare inline variables
                let 
                    // calculate the compelete years elapsed
                    DateDiffInYears = 
                        Date.Year ( [Date2] ) - Date.Year ( [Date1] ),
                    // calculate the remaining months
                    MonthsRemaining = 
                        Date.Month ( [Date2] ) - Date.Month ( [Date1] ),
                    // calculate the total number of months
                    DateDiffInMonths = 
                        ( DateDiffInYears * 12 ) + MonthsRemaining
                in
                    DateDiffInMonths < 15
        )
in
    SelectRowsLessThan15Months

[jennratten]

@joshua1990 If my post helped you please consider kudoing and accepting as a solution.

[AmiraBedh · Accepted solution]

In Power Query you can't filter a column based on another, but here is a woraround :

This is my initial dataset :

You can create a custom column to calculate the end date of the 15-month period from Date 1 :

Date.AddMonths([Date1], 15)

Then another  custom column  IsIn15Months to flag rows where Date2 is within 15 months of Date1 :

= Table.AddColumn(#"Added Custom", "Custom", each if [Date2] <= [End Date] then true else false)

Filter the rows where IsIn15Months is true.

[jennratten · Accepted solution]

Hello @joshua1990,

Your message doesn't say if you are trying to retain records in Date2 is greater than or less than 15 months from Date1 so I have given an example below which can solve for both. I have also used dates that can demonstrate the expected result.

In the screenshot below I have added a column which calculates the number of months between Date2 and Date1.  This creates the ability for you to then see the difference and filter based on your use case.  If seeing the difference as a separate column is not preferred then you can either remove the column or use the same logic in your filter statement.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJW0lEygjBNlGJ1UMSNcYibwMVjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date1 = _t, Date2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date1", type date}, {"Date2", type date}}),
    date_diff = Table.AddColumn(
                // starting table
            #"Changed Type",
            // name of new column to be created
            "MonthsBtwnDate2AndDate1", 
            each 
                // declare inline variables
                let 
                    // calculate the compelete years elapsed
                    DateDiffInYears = 
                        Date.Year ( [Date2] ) - Date.Year ( [Date1] ),
                    // calculate the remaining months
                    MonthsRemaining = 
                        Date.Month ( [Date2] ) - Date.Month ( [Date1] ),
                    // calculate the total number of months
                    DateDiffInMonths = 
                        ( DateDiffInYears * 12 ) + MonthsRemaining
                in
                    DateDiffInMonths,
                    Int64.Type
        )
in
    date_diff

  If you want to filter Date2 based on Date1 then it would be like this (retaining rows where Date2 is less than 15 months from Date1).

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJW0lEygjBNlGJ1UMSNcYibwMVjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date1 = _t, Date2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date1", type date}, {"Date2", type date}}),
    SelectRowsLessThan15Months = Table.SelectRows (
                // starting table
            #"Changed Type",
            each 
                // declare inline variables
                let 
                    // calculate the compelete years elapsed
                    DateDiffInYears = 
                        Date.Year ( [Date2] ) - Date.Year ( [Date1] ),
                    // calculate the remaining months
                    MonthsRemaining = 
                        Date.Month ( [Date2] ) - Date.Month ( [Date1] ),
                    // calculate the total number of months
                    DateDiffInMonths = 
                        ( DateDiffInYears * 12 ) + MonthsRemaining
                in
                    DateDiffInMonths < 15
        )
in
    SelectRowsLessThan15Months