urgent help needed in power query

[tkavitha911]

Hi Team, I need help with filtering dates in Power Query Editor. I have a requirement to filter records that fall After 3 months (including the current month and the following two months) and also those that fall after 9 months, based on a date column. The data contains only month and year values.

[burakkaragoz · Accepted solution]

Hi @tkavitha911 ,

You can achieve this in Power Query by creating a date column from your Year and Month values, then comparing that date to the current date with the required offset. Here’s a step-by-step example:

---

1. Combine Year and Month into a Date column

Assume your columns are [Year] and [Month]. Add a custom column with this formula:

m

= #date([Year], [Month], 1)

This will create a date at the start of each month.

---

2. Get today’s date and calculate offsets

Add two custom columns:

• For "After 3 months" (includes current month and next two months):

m

let
    CurrentDate = Date.From(DateTime.LocalNow()),
    StartMonth = Date.StartOfMonth(CurrentDate),
    EndMonth = Date.AddMonths(StartMonth, 2),
    ThisDate = #date([Year], [Month], 1)
in
    ThisDate >= StartMonth and ThisDate <= EndMonth

• For "After 9 months":

m

let
    CurrentDate = Date.From(DateTime.LocalNow()),
    CheckDate = Date.AddMonths(Date.StartOfMonth(CurrentDate), 9),
    ThisDate = #date([Year], [Month], 1)
in
    ThisDate > CheckDate

---

3. Filter your table

• Use the "After 3 months" column to filter for dates within the current and next two months.
• Use the "After 9 months" column to filter for dates after 9 months from now.

---

Summary of Steps:

1. Combine year and month into a date.
2. Add logical columns for your two filter conditions.
3. Filter your data based on those columns.

Let me know if you need a sample M code or further clarification!
translation and formatting supported by AI

[v-hashadapu]

Hi @tkavitha911 , I hope you're doing well! Just checking in to see if you had a chance to review the details shared earlier. If any of the information addressed your needs, feel free to mark it as "Accept as Solution" or give it a 'Kudos' to help others in the community. Please let me know if you have any further questions!

[v-hashadapu]

Hello @tkavitha911 , Just getting back to see if the shared details answered your question. If so, marking it as "Accept as Solution" and giving a 'Kudos' would be greatly appreciated to guide others in the community. Feel free to reach out with any additional questions!

[v-hashadapu]

Hi @tkavitha911 ,
I wanted to follow up and see if you’ve had a chance to review the information provided here by @burakkaragoz and @Thejeswar .
If any of the responses helped solve your issue, please consider marking it "Accept as Solution" and giving it a 'Kudos' to help others easily find it.
Let me know if you have any further questions!

[Nasif_Azam]

Hey @tkavitha911 ,

To filter dates in Power Query based on your requirements "next 3 months including current" and "after 9 months"  you can follow this process, assuming your date column only has Month and Year (e.g., 01-2025 or similar). 

1. Ensure your column is a proper date
If your column is text with just month and year, convert it to a date (assuming day as 1st of the month):

= Table.AddColumn(Source, "FullDate", each #date(Number.FromText([Year]), Number.FromText([Month]), 1), type date)

Or if it's in text like "Jan-2025":

= Table.AddColumn(Source, "FullDate", each Date.FromText("01-" & [MonthYear]), type date)

2. Add Current Date Reference
= Table.AddColumn(PreviousStep, "CurrentDate", each Date.From(DateTime.LocalNow()), type date)

Then extract the first day of the current month:

= Table.AddColumn(PreviousStep, "FirstDayOfCurrentMonth", each Date.StartOfMonth([CurrentDate]), type date)

3. Calculate Threshold Dates
= Table.AddColumn(PreviousStep, "EndOf3Months", each Date.AddMonths([FirstDayOfCurrentMonth], 3), type date),
Table.AddColumn(PreviousStep, "StartOf9Months", each Date.AddMonths([FirstDayOfCurrentMonth], 9), type date)

4. Filter the Table
Now apply a filter that keeps:

• Dates less than EndOf3Months
• Or dates greater than or equal to StartOf9Months

= Table.SelectRows(PreviousStep, each
[FullDate] < [EndOf3Months] or
[FullDate] >= [StartOf9Months])

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.

Best Regards,
Nasif Azam

[Thejeswar · Accepted solution]

Hi @tkavitha911 ,

As rightly said by @burakkaragoz , you may have to use Custom Column to flag those days which fall in current month and next two month and a separate custom column to flag those records that fall after 9 months.

In case you want to have this flag in the same custom column, you may have to use the below logic inside the custom column.

let
    CurrentDate = Date.From(DateTime.LocalNow()),
    StartMonth = Date.StartOfMonth(CurrentDate),
    EndMonth = Date.AddMonths(StartMonth, 3),
    After9Months = Date.AddMonths(StartMonth,9)
    ThisDate = #date([Year], [Month], 1)
in
    (ThisDate >= StartMonth and ThisDate <= EndMonth) or  ThisDate >= After9Months

or if you have a separate Date Column available you can use the below Logic

let
    CurrentDate = Date.From(DateTime.LocalNow()),
    StartMonth = Date.StartOfMonth(CurrentDate),
    EndMonth = Date.AddMonths(StartMonth, 3),
    After9Months = Date.AddMonths(StartMonth,9)
in
    ([Dates] >= StartMonth and [Dates] <= EndMonth) or  [Dates] >= After9Monthsac

Regards,

[burakkaragoz · Accepted solution]

Hi @tkavitha911 ,

You can achieve this in Power Query by creating a date column from your Year and Month values, then comparing that date to the current date with the required offset. Here’s a step-by-step example:

---

1. Combine Year and Month into a Date column

Assume your columns are [Year] and [Month]. Add a custom column with this formula:

m

= #date([Year], [Month], 1)

This will create a date at the start of each month.

---

2. Get today’s date and calculate offsets

Add two custom columns:

• For "After 3 months" (includes current month and next two months):

m

let
    CurrentDate = Date.From(DateTime.LocalNow()),
    StartMonth = Date.StartOfMonth(CurrentDate),
    EndMonth = Date.AddMonths(StartMonth, 2),
    ThisDate = #date([Year], [Month], 1)
in
    ThisDate >= StartMonth and ThisDate <= EndMonth

• For "After 9 months":

m

let
    CurrentDate = Date.From(DateTime.LocalNow()),
    CheckDate = Date.AddMonths(Date.StartOfMonth(CurrentDate), 9),
    ThisDate = #date([Year], [Month], 1)
in
    ThisDate > CheckDate

---

3. Filter your table

• Use the "After 3 months" column to filter for dates within the current and next two months.
• Use the "After 9 months" column to filter for dates after 9 months from now.

---

Summary of Steps:

1. Combine year and month into a date.
2. Add logical columns for your two filter conditions.
3. Filter your data based on those columns.

Let me know if you need a sample M code or further clarification!
translation and formatting supported by AI