Power Query - rolling n months, data from a n months ago, for one calendar month

[ewakol]

Hello,

There is a table and there is a column that's called GeneractionDate. And this column has dates format DD-MM-YYYY. How do i create a query which give me below result:

1. We are in Sep-24, i need to see all data from Jul-24

2. We are in Sep-24, i need to see all data from may-24

Resumming, I need a formula in Power Query that, in relation to a GeneractionDate, will show values for one calendar month that was 2, 3, n, months ago.

[ewakol]

This is it! Thanks!

[Anonymous]

Hi @ewakol ,

Suppose you exist such a table called Sales:

Where GeneractionDate column is of text type (because the default format of date in my region is MM/DD/YYYYY, I can't set this column directly to date type), the format is DD-MM-YYYYY.
Create a blank query:

And put all of the M code into the Advanced Editor:

let
    TodayDate = DateTime.LocalNow(),
    DateTwoMonthsAgo = Date.AddMonths(TodayDate, -2),
    YearOfDateTwoMonthsAgo = Date.Year(DateTwoMonthsAgo),
    MonthOfDateTwoMonthsAgo = Date.Month(DateTwoMonthsAgo),

    FilteredRows = Table.SelectRows(Sales, each let
        dateParts = Text.Split([GeneractionDate], "-"),
        year = Number.From(dateParts{2}),
        month = Number.From(dateParts{1})
        in year = YearOfDateTwoMonthsAgo and month = MonthOfDateTwoMonthsAgo
    )
in
    FilteredRows

This will show all data corresponding to two months ago, i.e., July-24:

If you want to see the data for other months, you just need to change the number 2 here:

DateTwoMonthsAgo = Date.AddMonths(TodayDate, -2),

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sanalytics]

Hello @ewakol 

Below is the complete solution of your problem.

I have given you the two solutions.
i) Creating a Parameter --> You can put any number you want and based on that your list of date will get filtered.
ii) Creating a Dynamic Function. -> Similary any number you can put and it will generate separate table for you.

Both solution attached in the file below

https://we.tl/t-VISNVHhqfq

Hope it helps

Regards

sanalytics

If it is your solution then please like and accept it as solution

[suparnababu8 · Accepted solution]

@ewakol 

step1 - add a custom column called FilterDate

Date.FromText([GeneractionDate], "dd-MM-yyyy")

step-2 - Add another custom column to calculate the date for last three months 

Date.AddMonths(Date.FromText([GeneractionDate], "dd-MM-yyyy"), -2)

Step-3 - Then filter the data  for two months

let
    Source = TableName,
    ChangedType = Table.TransformColumnTypes(Source,{{"GeneractionDate", type date}}),
    FilteredRows = Table.SelectRows(ChangedType, each Date.IsInPreviousNMonths([GeneractionDate], 2) and Date.IsInCurrentMonth([GeneractionDate]) = false)
in
    FilteredRows

Step-4 - Then filter the data  for three months  

let
    Source = TableName,
    ChangedType = Table.TransformColumnTypes(Source,{{"GeneractionDate", type date}}),
    FilteredRows = Table.SelectRows(ChangedType, each Date.IsInPreviousNMonths([GeneractionDate], 3) and Date.IsInPreviousNMonths([GeneractionDate], 2) = false)
in
    FilteredRows

Pls let me know if it works