How to filter the data of a date column from 150 days to today-1 in Power Query

[Phani_Borusu]

I have a query like this in Oracle

WHERE (

Order_Date BETWEEN TRUNC(sysdate-150,'MM') AND TRUNC(sysdate)-1

)

Output data would be from : 01-04-2023 to 11-09-2023  -- DD-MM-YYYY Format

I am trying to filter data in power query like this.

Create_dt must be between 150 days ago from today and today-1. But the month which falls for 150 minus days should take from the 1st day i.e., start of month. When I use StartOfMonth in Power query, for each record date it is getting changed and considering that month's first day.

Could anyone help me with a condition or solution to achieve the filtered data?

Data must be from 01-04-2023 to 11-09-2023 considering today's date i.e., 12-09-2023

Table.SelectRows(#"Changed Type", each Date.StartOfMonth(Date.IsInPreviousNDays([SaleDate], 150)) or Date.IsInPreviousNDays([SaleDate], 1))

I tried like this but it didn't work.

[Phani_Borusu]

@123abc Hi.. I got the solution. Thank you for your help. 🙂

[Phani_Borusu]

@123abcThank you for your suggestion. The problem I am facing when I use the given code is " the from date is just cosidered only 150 days ago from today i.e., 15-APR-2023 but I want data from 01-APR-2023.

The FROM date must be the first day of the month after differencing 150 days.

If April is the month and the date before 150 days from today is 15-04-2023 then it should be taken as 01-04-2023

If June is the month and the date before 150 days from someday is 15-06-2023 then it should be taken as 01-06-2023

I am unable to get start of month when using the code provided by you or by using my code.

Please suggest.

[123abc · Accepted solution]

To filter a date column from 150 days ago to today-1 in Power Query (M language), you can use the following steps:

1. Open Power Query Editor.

2. Select the column containing your date values (let's assume it's called "Order_Date").

3. Go to the "Add Column" tab and click on "Custom Column."

4. In the "Custom Column" dialog box, enter a name for the new column (e.g., "DateFilter").

5. In the "Custom column formula" box, enter the following formula:

```M
if Date.From([Order_Date]) >= Date.From(Date.From(DateTime.LocalNow()) - #duration(150, 0, 0, 0)) and Date.From([Order_Date]) <= Date.From(DateTime.LocalNow() - #duration(1, 0, 0, 0)) then "Yes" else "No"
```

This formula checks if each date in the "Order_Date" column falls within the desired date range of 150 days ago to today-1 and assigns "Yes" if it does or "No" if it doesn't.

6. Click the "OK" button to create the new column.

7. Now, go to the "Filter" dropdown for the "DateFilter" column and select "Yes" to filter the rows where the date falls within the desired range.

This will filter the data from 01-04-2023 to 11-09-2023, considering today's date as 12-09-2023.

Keep in mind that Power Query operates in a step-by-step manner, and creating a custom column for filtering can be a more robust approach when dealing with complex date filtering requirements.