Forum Discussion

ewakol's avatar
ewakol
Icon for Helper II rankHelper II
2 years ago
Solved

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

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.

Need Help
Show and Tell
  • suparnababu8's avatar
    suparnababu8
    2 years ago

    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

     

      

4 Replies

  • suparnababu8's avatar
    suparnababu8
    Icon for Super User rankSuper User
    2 years ago

    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

     

      

  • sanalytics's avatar
    sanalytics
    Icon for Super User rankSuper User
    2 years ago

    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

     

     

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.