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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
mlovejoy
Helper I
Helper I

DAX (add column) identify the max date of grouping

Hello community,

 

My problematic is straight forward I need to add DAX column. I have cirteria x, y and z. I can identify duplicate with this formula:

Duplicates count =

var x= Criteria
return
COUNTROWS(FILTER(Table,criteria = x)) this give me the number of duplicates (grouping).

Then for every duplicates I need to put a 1 to the most recent date of the grouping and a 0 to the oldest.

Is that even possible to do in DAX add Column?

Screenshot 2022-12-16 at 15.28.23.png

 

Thanks for your help,

Mark Lovejoy

1 ACCEPTED SOLUTION
TomasAndersson
Solution Sage
Solution Sage

Hi!

You should be able to do something like this:

Most recent date = 
VAR __IsDuplicate = //Checks if row has a duplicate. If all rows has duplicates, this is not needed.
    If('Table'[Duplicates]>0,TRUE(),FALSE())
    
VAR __MaxDate =  //Finds the most recent (MAX()) date for current criteria
    CALCULATE(
        max('Table'[Date]),
        FILTER(all('Table'),'Table'[Criteria] = EARLIER('Table'[Criteria])
        )
    )
return
if(__IsDuplicate,   //If current row is a duplicate
    if('Table'[Date] = __MaxDate,  //And current row date is the most recent date for this criteria
        1, //return 1
        0  //else return 0
    ),
    0 else return 0
)

Hope this helps!

View solution in original post

2 REPLIES 2
tamerj1
Super User
Super User

Hi @mlovejoy 

please try

Result =
MAXX (
CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[Criteria] ) ),
'Table'[Date]
)

TomasAndersson
Solution Sage
Solution Sage

Hi!

You should be able to do something like this:

Most recent date = 
VAR __IsDuplicate = //Checks if row has a duplicate. If all rows has duplicates, this is not needed.
    If('Table'[Duplicates]>0,TRUE(),FALSE())
    
VAR __MaxDate =  //Finds the most recent (MAX()) date for current criteria
    CALCULATE(
        max('Table'[Date]),
        FILTER(all('Table'),'Table'[Criteria] = EARLIER('Table'[Criteria])
        )
    )
return
if(__IsDuplicate,   //If current row is a duplicate
    if('Table'[Date] = __MaxDate,  //And current row date is the most recent date for this criteria
        1, //return 1
        0  //else return 0
    ),
    0 else return 0
)

Hope this helps!

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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