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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
luckygirl
Frequent Visitor

Sort a table before calculate based on date

I have a table like below.

CodeCompletedDateProductDetail
11/01/2018A
11/04/2019B
123/05/2021C
29/02/2018D
25/05/2021C
26/05/2022A
31/01/2018K
310/05/2019B
330/06/2021C
31/01/2024L
46/06/2017B
49/03/2018J
45/05/2021C
43/03/2024D

 

I need to create a Target column based on CompletedDate for Each Code group. So the expected solution would be like below.

CodeCompletedDateProductDetailTarget
11/01/2018AB
11/04/2019BC
123/05/2021C 
29/02/2018DC
25/05/2021CA
26/05/2022A 
31/01/2018KB
310/05/2019BC
330/06/2021CL
31/01/2024L 
46/06/2017BJ
49/03/2018JC
45/05/2021CD
43/03/2024D 

 

I tried the below DAX code. It generates correct results for some but not all. Can someone please help me. Thanks.

Target =
CALCULATE (
    MIN ( Tbl[ProductDetail] ),
    FILTER (
        'Tbl',
        Tbl[ClientCode] = EARLIER ( Tbl[ClientCode] )
         && Tbl[CompletedDate] > EARLIER ( Tbl[CompletedDate] )
    )
)

The results by the above code. Incorrect ones are highlighted in red.
CodeCompletedDateProductDetailTarget
11/01/2018AB
11/04/2019BC
123/05/2021C 
29/02/2018DA
25/05/2021CA
26/05/2022A 
31/01/2018KB
310/05/2019BC
330/06/2021CL
31/01/2024L 
46/06/2017BC
49/03/2018JC
45/05/2021CD
43/03/2024D 


1 ACCEPTED SOLUTION
Irwan
Super User
Super User

hello @luckygirl 

 

please check if this accomodate your need.

Irwan_0-1737083796882.png

create a new calculated column with following DAX

Target = 
var _Min =
MINX(
    FILTER(
        'Table',
        'Table'[CompletedDate]>EARLIER('Table'[CompletedDate])&&
        'Table'[Code]=EARLIER('Table'[Code])
    ),
    'Table'[CompletedDate]
)
Return
MINX(
    FILTER(
        'Table',
        'Table'[CompletedDate]=_Min
    ),
    'Table'[ProductDetail]
)
 
Hope this will help.
Thank you.

View solution in original post

3 REPLIES 3
luckygirl
Frequent Visitor

@Irwan Thanks a lot. Works well 🙂

Hello @luckygirl 

 

Glad to be a help.

Thank you.

Irwan
Super User
Super User

hello @luckygirl 

 

please check if this accomodate your need.

Irwan_0-1737083796882.png

create a new calculated column with following DAX

Target = 
var _Min =
MINX(
    FILTER(
        'Table',
        'Table'[CompletedDate]>EARLIER('Table'[CompletedDate])&&
        'Table'[Code]=EARLIER('Table'[Code])
    ),
    'Table'[CompletedDate]
)
Return
MINX(
    FILTER(
        'Table',
        'Table'[CompletedDate]=_Min
    ),
    'Table'[ProductDetail]
)
 
Hope this will help.
Thank you.

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!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

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