Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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?
Solved! Go to 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
If this post helps to answer your questions, please consider marking it as a solution so others can find it more quickly when faced with a similar challenge.
Proud to be a Microsoft Fabric Super User
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.
@joshua1990 If my post helped you please consider kudoing and accepting as a solution.
If this post helps to answer your questions, please consider marking it as a solution so others can find it more quickly when faced with a similar challenge.
Proud to be a Microsoft Fabric Super User
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.
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
If this post helps to answer your questions, please consider marking it as a solution so others can find it more quickly when faced with a similar challenge.
Proud to be a Microsoft Fabric Super User
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
9 | |
8 | |
7 | |
6 | |
6 |
User | Count |
---|---|
12 | |
11 | |
9 | |
6 | |
6 |