Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Group Data in Split Months

I'm trying to group an invoice period in starting on the 14th of a month and ending on the 13th of the following month.  

 

Date                   GROUP

5/14/2021GROUP 1
5/15/2021GROUP 1
…GROUP 1
6/12/2021GROUP 1
6/13/2021GROUP 1
6/14/2021GROUP 2
6/15/2021GROUP 2
…GROUP 2
7/12/2021GROUP 2
7/13/2021GROUP 2

 

Thanks in advance.

Need Help
  • Fowmy's avatar
    Fowmy
    5 years ago

    Anonymous 

    You can add the following code as a new Calculated Column:

    Group DAX = 
var __group = 
    RANKX(
        ALL('Table'),
        var __date = [Date] return
        IF( DAY(__date) < 14, EOMONTH(__date,-2)+14, EOMONTH(__date,-1)+14 )
        ,,ASC,Dense
    )   
return
    "Group " & __group

     



5 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      That is Awesome!  However, the source is a calculated table.  Can it be done in DAX?

      • Fowmy's avatar
        Fowmy
        Super User
        5 years ago

        Anonymous 

        I will try and share the code soon. 👍 

  • CNENFRNL's avatar
    CNENFRNL
    Community Champion
    5 years ago

    Table.Group() does the trick,

    let
    Source = Table.FromList(List.Dates(#date(2021,5,14),55,#duration(1,0,0,0)), Splitter.SplitByNothing(), {"Date"}),
    #"Grouped Rows" = Table.Group(Source, {"Date"}, {"Group", each _}, 0, (x,y) => Number.From(Date.Day(y[Date])=14))
in
    #"Grouped Rows"