cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mattcameron Frequent Visitor
Frequent Visitor

Filter by first x days per group

My question is, is there a way to get the first/earliest date per grouping in a table and then filter the table to only include rows within the first x number of months of that first date, per grouping. Probably easiest to ask with example. Say I have the following table, and want to keep data for first 6 months of each Group:

 

1.png

 

The resulting table would look like:

 

2.png

 

Is there a way to do this using DAX/M?

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Filter by first x days per group

Hello @mattcameron

 

try like this:

 

FILTER(
    'Table',
    EDATE( [Date], -6 ) <=   CALCULATE( MIN( 'Table'[Date] ), ALLEXCEPT( 'Table', 'Table'[Group] ) )
)

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

3 REPLIES 3
Super User
Super User

Re: Filter by first x days per group

Hello @mattcameron

 

try like this:

 

FILTER(
    'Table',
    EDATE( [Date], -6 ) <=   CALCULATE( MIN( 'Table'[Date] ), ALLEXCEPT( 'Table', 'Table'[Group] ) )
)

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

mattcameron Frequent Visitor
Frequent Visitor

Re: Filter by first x days per group

@LivioLanzothanks for your response. Where would I actually enter this code? Can you possibly provide PBIX file example?

Super User
Super User

Re: Filter by first x days per group

@mattcameron

 

It is a DAX query which returns a table so you'd go to the 'Modeling' tab and click on 'New Table'

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!