Forum Discussion

CaitlinMc's avatar
CaitlinMc
Frequent Visitor
2 years ago

How to determine Last Status

Good afternoon!   I am trying to create an accountability report that shows the Department that owns the last Action Status for each Job Number.  Our table (WORK_MGT_REQUIREMENTS) has over 300 Act...
Need Help
CaitlinMc's avatar
CaitlinMc
Frequent Visitor
2 years ago

Anonymous Thank you for the calculation. The calculation is working to show me the absolute last date that the job was touched, however, I am still having the following issue:

Job # 420430 appears in the Operations/Crew Supervisors bucket showing that the Last Requirement was 060 Schedule Job and Last Action Status was Schedule Job which was done on 2/20/2024 (all of this is correct).

However, that same job is appearing in the Staking/Engineering bucket with the same date (which isn't the case):


My expected result is to see Job #420430 to appear in the Operations/Crew Supervisors bucket (as that was the last department to touch it) and not in any other department's bucket, unless multiple departments touched it on the same day. Thank you in advance!

Anonymous's avatar
Anonymous
Not applicable
2 years ago

HI CaitlinMc,

Sure, I also added the condition to check requirement field , you can try it if suitable for your requirement:

flag =
VAR currDate =
    MAX ( Table1[Action Date] )
VAR _lastdate =
    CALCULATE (
        MAX ( Table1[Action Date] ),
        ALLSELECTED ( Table1 ),
        VALUES ( Table1[Job Number] ),
        VALUES ( Table1[Department] )
    )
VAR reqList=
    CALCULATETABLE (
        ADDCOLUMNS (
            VALUES ( Table1[Requirement] ),
            "Step", VALUE ( LEFT ( [Requirement], 4 ) )
        ),
        FILTER ( ALLSELECTED ( Table1 ), [Action Date] = _lastdate )
    )
VAR currReq =
    SELECTEDVALUE ( Table1[Requirement] )
VAR lastReq =
    MAXX (
        FILTER ( reqList, [Step] = MAXX ( reqList, [Step] ) ),
        [Requirement]
    )
RETURN
    IF ( currDate = _lastdate && currReq = lastReq, "Y", "N" )

Regards,

Xiaoxin Sheng