Group Data in Split Months

[Anonymous]

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/2021	GROUP 1
5/15/2021	GROUP 1
…	GROUP 1
6/12/2021	GROUP 1
6/13/2021	GROUP 1
6/14/2021	GROUP 2
6/15/2021	GROUP 2
…	GROUP 2
7/12/2021	GROUP 2
7/13/2021	GROUP 2

Thanks in advance.

[CNENFRNL]

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"

[Fowmy]

@Anonymous 

You can do it in Power Query. I attached a file, please check the steps.

[Anonymous]

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

[Fowmy]

@Anonymous 

I will try and share the code soon. 👍 

[Fowmy · Accepted solution]

@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