Forum Discussion

ewakol's avatar
ewakol
Icon for Helper II rankHelper II
1 year 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,...
  • suparnababu8's avatar
    1 year 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