Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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.

1 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

Fowmy_0-1626468632666.png

 



Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

5 REPLIES 5
CNENFRNL
Community Champion
Community Champion

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"

Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

Fowmy
Super User
Super User

@Anonymous 

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

Fowmy_0-1626457649381.png



 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Anonymous
Not applicable

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

@Anonymous 

I will try and share the code soon. 👍 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

@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_0-1626468632666.png

 



Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.