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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
dkpcr5
Helper II
Helper II

Help getting latest date for earlier value

I have a table containing Dates when Responsibility gets set, see below.

 

This Responsibility Set Date produces very close to what I'm trying to achieve: I want to capture the CreateDate any time the Responsibility changes:

 

Capture.PNG

But in cases where there are multiple records per responsibility, the _current (MIN) variable reverts back to the initial CreateDate. So, I get why it's producing the results it is, but am not sure how to get it to produce the desired results.

For the rows with red x's (Index rows 8 and 10), I need to see:

Index row 8 = 12/14/2022 3:39:28 PM (capturing the CreateDate of row 7 at Responsibility change)

Index row 10 = 12/20/2022 3:53:28 PM (capturing the CreateDate of row 9 at Responsibility change)

 

Here's the dax currently generating the Responsibility Set Date results. I'm sure this is a simple fix, but it's been vexing for hours. Any help would be appreciated!

Responsibility Set Date = 
VAR previous =
CALCULATE(
    DISTINCT(LOA_Logs[Responsibility]),
    LOA_Logs[Index per LOA] = (EARLIER(LOA_Logs[Index per LOA]) - 1),
    ALLEXCEPT(LOA_Logs, LOA_Logs[LOA_UID], LOA_Logs[Responsibility])
)
VAR _current =
CALCULATE(MIN(LOA_Logs[CreateDate]),
FILTER(LOA_Logs,
LOA_Logs[LOA_UID] = EARLIER(LOA_Logs[LOA_UID]) &&
LOA_Logs[Responsibility] = EARLIER(LOA_Logs[Responsibility]))
)

RETURN
IF(LOA_Logs[Responsibility] <> previous, LOA_Logs[CreateDate], _current)

 

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

Hi @dkpcr5 
First create a calculated column as follows

Index2 =
LOA_Logs[Index per LOA]
    - RANKX (
        CALCULATETABLE (
            LOA_Logs,
            ALLEXCEPT ( LOA_Logs, LOA_Logs[LOA_UID], LOA_Logs[Responsibility] )
        ),
        LOA_Logs[Index per LOA],
        ,
        ASC,
        DENSE
    )

The the following can be either a measure or a calculated column

Responsibility Set Date =
MINX (
    CALCULATETABLE (
        LOA_Logs,
        ALLEXCEPT ( LOA_Logs, LOA_Logs[LOA_UID], LOA_Logs[Index2] )
    ),
    LOA_Logs[CreateDate]
)

View solution in original post

4 REPLIES 4
tamerj1
Super User
Super User

Hi @dkpcr5 
First create a calculated column as follows

Index2 =
LOA_Logs[Index per LOA]
    - RANKX (
        CALCULATETABLE (
            LOA_Logs,
            ALLEXCEPT ( LOA_Logs, LOA_Logs[LOA_UID], LOA_Logs[Responsibility] )
        ),
        LOA_Logs[Index per LOA],
        ,
        ASC,
        DENSE
    )

The the following can be either a measure or a calculated column

Responsibility Set Date =
MINX (
    CALCULATETABLE (
        LOA_Logs,
        ALLEXCEPT ( LOA_Logs, LOA_Logs[LOA_UID], LOA_Logs[Index2] )
    ),
    LOA_Logs[CreateDate]
)

That did it @tamerj1  - thanks so much for the assist!

amitchandak
Super User
Super User

@dkpcr5 , Use Max in place of Min

 

Responsibility Set Date =
VAR previous =
CALCULATE(
DISTINCT(LOA_Logs[Responsibility]),
filter(LOA_Logs, LOA_Logs[Index per LOA] = (EARLIER(LOA_Logs[Index per LOA]) - 1) &&
LOA_Logs[LOA_UID] = earlier(LOA_Logs[LOA_UID]) && LOA_Logs[Responsibility] = earlier(LOA_Logs[Responsibility]) )
)
VAR _current =
CALCULATE(MAX(LOA_Logs[CreateDate]),
FILTER(LOA_Logs,
LOA_Logs[LOA_UID] = EARLIER(LOA_Logs[LOA_UID]) &&
LOA_Logs[Responsibility] = EARLIER(LOA_Logs[Responsibility]))
)

RETURN
IF(LOA_Logs[Responsibility] <> previous, LOA_Logs[CreateDate], _current)

Thanks for the assist @amitchandak, though unfortunately, this doesn't work. When I change MIN to MAX, I get a changing "Responsibility Set Date" even when the Responsibility field isn't changing. See below. In this screenshot, I'd expect rows 8-14 to all have Responsibility Set Date = 1/10/2023 3:59:38 PM.

Open to other suggestions - thanks again!

Capture1.PNG

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors