How to determine Last Status

[CaitlinMc]

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 Action Statuses that are assigned to a Department and fit into a subcategory called Requirements.  

Here is a look at a quick sample of data:

Table: WORK_MGT_REQUIREMENTS
Job Number	Requirement	Action Status	Status	Action Date	Department
1	021 Staking Design	Assigned for Field Evaluation/Design	Created	10/18/2023	Staking
2	021 Staking Design	Site Visit Complete	Completed	11/1/2023	Staking
3	060 Schedule Job	Assigned to Crew	Completed	12/2/2023	Crews
4	021 Staking Design	Assigned for Field Evaluation/Design	Created	1/13/2024	Staking
1	021 Staking Design	Assign to Cont 1	Assigned	10/18/2023	Staking
2	060 Schedule Job	Schedule Job	Created	11/2/2023	OES
3	100 Reconcile Material	Reconcile Material As Built	Created	12/3/2023	OES
4	021 Staking Design	Assign to Cont 4	Assigned	1/14/2024	Staking
1	021 Staking Design	Site Visit Complete	Completed	10/19/2023	Staking
2	060 Schedule Job	Assigned to Crew	Null	Null	Null
3	100 Reconcile Material	Complete Job	Completed	12/5/2023	Warehouse
4	021 Staking Design	Accepted by Cont 4	In Process	1/14/2024	Staking
1	031 Locates	Locates Required	Created	10/19/2023	OES
3	110 Mapping	Review As Built Staking Sheets	Created	12/5/2023	Warehouse

So far I have:

• Created an index table.
• Combined the numbered Requirement and Action Status fields to create a new column, Requirement + Action Status.
• Ordered each unique Requirement + Action Status option.
• Merged that index table with my current WORK_MGT_REQUIREMENTS table.
• Sorted the Requirement + Action Status column by the index.
• Grouped Requirement + Action Status column into departments who created the Requirement + Action Status.

When I created a spreadsheet visual and a slicer based on the Department column, the last Requirement & Action Status appeared under each department.

If I select Warehouse:

Job Number	Last Requirement	Last Action Status	Last Status	Last Action Date	Current Department
3	110 Mapping	Review As Built Staking Sheets	Created	12/5/2023	Warehouse

 If I select Crews: 

Job Number	Last Requirement	Last Action Status	Last Status	Last Action Date	Current Department
3	060 Schedule Job	Assigned to Crew	Completed	12/2/2023	Crews

The slicer is displaying the last Action Date a Requirement + Action Status in that Department occurred rather than the last overall Action Date for that Job Number.

So, with the example above Job Number 3 is no longer the Crews responsibility, it progressed to the Warehouse 3 days after.

[CaitlinMc]

@v-shex-msft 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!

[v-shex-msft]

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

[v-shex-msft]

Hi @CaitlinMc,

You can create a measure formula to compare records and return flag. Then you can create table visual and use this measure formula on ‘visual level filter’ to filter records:

flag =
VAR currDate =
    MAX ( Table1[Action Date] )
VAR _lastdate =
    CALCULATE (
        MAX ( Table1[Action Date] ),
        ALLSELECTED ( Table1 ),
        VALUES ( Table1[Job Number] ),
        VALUES ( Table1[Department] )
    )
RETURN
    IF ( currDate = _lastdate, "Y", "N" )

Regards,

Xiaoxin Sheng