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

     



Fowmy's avatar
Fowmy
Super User
5 years ago

Anonymous 

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



 

  • 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. 👍 

      • Fowmy's avatar
        Fowmy
        Super User
        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