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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
CaitlinMc
Frequent Visitor

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 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.

3 REPLIES 3
CaitlinMc
Frequent Visitor

@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).

CaitlinMc_0-1709224389302.png

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

CaitlinMc_1-1709224542870.png


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!

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors