Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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 ...
"DAX"
"Help"
"Need Help"
  • tamerj1's avatar
    tamerj1
    3 years ago

    Hi Anonymous 
    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]
)
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
3 years ago

Anonymous , 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)

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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!