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/2021 GROUP 1 5/15/2021 GROUP 1...
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

     



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"