Forum Discussion

harrisward's avatar
harrisward
New Member
4 years ago

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 currently in. In the screenshot below is an example of the output im looking for. I have removed individual dates between the start date and end date of each month ( and replaced them with ...) to save space but its worth pointing out that each row in the date dimension represents a single day

 

As we are in August all the dates in August are set to Month 0 in the new column (Artificial Quarter). From there we want to look at the previous three months from the currecnt month and "group them" into the new column with a name like "Month 1 to 3", the next previous three months would be grouped into "Month 4 to 6" and so on.

 

In the end I want to simply group 3 month chunk of dates into these groups based on the month we are currectly in

Need Help

2 Replies

  • ronrsnfld's avatar
    ronrsnfld
    Super User
    4 years ago

    Not 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.

     

     

  • DataInsights's avatar
    DataInsights
    Super User
    4 years ago

    harrisward,

     

    Try this custom column in Power Query:

     

    if Date.IsInCurrentMonth([Date]) then
  "Month 0"
else if [Date]
  >= Date.AddMonths(Date.StartOfMonth(DateTime.Date(DateTime.LocalNow())), -3) and [Date]
  <= Date.AddDays(Date.StartOfMonth(DateTime.Date(DateTime.LocalNow())), -1)
then
  "Month 1 to 3"
else if [Date]
  >= Date.AddMonths(Date.StartOfMonth(DateTime.Date(DateTime.LocalNow())), - 6) and [Date]
  <= Date.AddDays(Date.AddMonths(Date.StartOfMonth(DateTime.Date(DateTime.LocalNow())), -3), -1)
then
  "Month 4 to 6"
else if [Date]
  >= Date.AddMonths(Date.StartOfMonth(DateTime.Date(DateTime.LocalNow())), - 9) and [Date]
  <= Date.AddDays(Date.AddMonths(Date.StartOfMonth(DateTime.Date(DateTime.LocalNow())), -6), -1)
then
  "Month 7 to 9"
else if [Date]
  >= Date.AddMonths(Date.StartOfMonth(DateTime.Date(DateTime.LocalNow())), - 12) and [Date]
  <= Date.AddDays(Date.AddMonths(Date.StartOfMonth(DateTime.Date(DateTime.LocalNow())), -9), -1)
then
  "Month 10 to 12"
else
  null