urgent help needed in power query

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.

Need Help

burakkaragoz
1 year ago
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:
Combine year and month into a date.
Add logical columns for your two filter conditions.
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

Thejeswar

1 year ago

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,

6 Replies

Thejeswar

Super User

1 year ago

Hi tkavitha911 ,

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
Super User
1 year ago
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:
Combine year and month into a date.
Add logical columns for your two filter conditions.
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
Nasif_Azam
Super User
1 year ago
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
v-hashadapu
Community Support
1 year ago
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!
v-hashadapu
Community Support
1 year ago
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
Community Support
1 year ago
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!

Forum Discussion

urgent help needed in power query

6 Replies

Related content

Need Urgent Help on SamePeriodLastYear

urgent help needed

Need Urgent help

urgent help needed

urgent help needed