Forum Discussion
harrisward
4 years agoNew Member
Dynamically Grouping Months Based on Todays Month
Hey folks. I have a problem where I need to create a new column on my date table (dimension) using Power Query that will group my dates into 3 month "artificial" quarters based on the month we are cu...
ronrsnfld
Super User
4 years agoNot sure how far back you want to go with your grouping, but try the below code:
Note the List argument in the List.Accumulate function -- change the upper bound depending on how many months back you wish to go
let
//create a two year table for testing
Source = Table.FromColumns({List.Dates(#date(2021,1,1),730,#duration(1,0,0,0))}, type table [Date=date]),
#"Added Custom" = Table.AddColumn(Source, "Quarter", each
let
pos = List.PositionOf(
//change list argument in List.Accumulate depending on how many months back you want this to go
List.Accumulate({0..24},{}, (state, current)=> state & {
Date.IsInPreviousNMonths([Date], current)}),true),
qtr = if pos = -1 then "Month 0" else
"Month" & Text.From(Number.IntegerDivide(pos-1,3) * 3 + 1) & "-" &
Text.From(Number.IntegerDivide(pos-1,3) * 3 + 3)
in
qtr)
in
#"Added Custom"
In this screenshot, I have filtered the results to just show the first of each month, but the original table has a row for every date.