How calculate a column based on

[ddeutschman]

I need to calculate the Number of days since either

1.  Start of a Job

2.  Since the last Type of Event

There is a relationship between the Job Table and Event table.  There may be  multiple Events for a job for there may be none.  If there are no events, then I want to set the date of the last event to the Date the Job started otherwise set the Date to the most recent Event Date.  The following dows not work as it gives me the Earliest date for All events. 

Date Last Recordable = IF (MIN('UMC Safety Incident Log'[Date of Incident]) < udJob[StartDate], udJob[StartDate], MIN('UMC Safety Incident Log'[Date of Incident]))

What is the correct syntax for the formla?

[v-ljerr-msft]

Hi @ddeutschman,

Could you try the formula below to see if it works in your scenario? 

Date Last Recordable =
IF (
    ISBLANK ( CALCULATE ( MIN ( 'UMC Safety Incident Log'[Date of Incident] ) ) ),
    udJob[StartDate],
    CALCULATE ( MIN ( 'UMC Safety Incident Log'[Date of Incident] ) )
)

Regards

[ddeutschman]

Sorry, I have been out of the office and unable to test the function.

That worked but I realized that I need to base the calculation on another column, UMC Status in UMC Safety Incident Log.  I tried the following but get the error that a single value for the column UMC Status can not be determined. 

Date Last Recordable = IF ('UMC Safety Incident Log'[UMC Status] = "Recordable", IF(ISBLANK ( CALCULATE ( MIN ( 'UMC Safety Incident Log'[Date of Incident] ) ) ), udJob[StartDate], CALCULATE ( MIN ( 'UMC Safety Incident Log'[Date of Incident] ) ) ))

Thanks in advance for your assistance.  I am a newbie and am still learning how to build formulas.

[ddeutschman]

Can I use the Related function to only return the rows in UMC Safety Incident Log where the column UMC Status = "Recordable" in the formula?  There is a direct relationship between the udJob and UMC Safety Incident Log tables.  If so, how would that look?

[v-ljerr-msft]

Hi @ddeutschman,

Could you try the formula below to see if it works in your new scenario? 

Date Last Recordable =
IF (
    CALCULATE ( FIRSTNONBLANK ( 'UMC Safety Incident Log'[UMC Status], 1 ) )
        = "Recordable",
    IF (
        ISBLANK ( CALCULATE ( MIN ( 'UMC Safety Incident Log'[Date of Incident] ) ) ),
        udJob[StartDate],
        CALCULATE ( MIN ( 'UMC Safety Incident Log'[Date of Incident] ) )
    )
)

Regards

[ddeutschman]

No.  It did not return the the last Recordable Date for all Jobs.   It did for only one - 6881.  The FIRSTNONBLANK function is not finding the last entry in the data set.

I have an excel file with a subset of the jobs so you can see an example of the data that is in the UMC Safety Incident Log table.   How can I upload the file?

[ddeutschman]

Here is an example of the data:

UMC Status	Date of Incident	Project Number
	10/26/14	6262
UMC First Aid	01/21/15	6262
Recordable	01/27/15	6262
UMC First Aid	06/18/15	6262
UMC First Aid	09/03/15	6262
	10/08/15	6262
Recordable	01/14/15	6265
Recordable	07/19/16	6623
Notification	08/09/16	6623
Notification	10/03/16	6623
Notification	10/07/16	6623
	12/01/16	6623
Notification	05/15/17	6623
Recordable	06/16/17	6623
UMC First Aid	06/29/17	6623
UMC First Aid	03/02/17	6881
Recordable	04/21/17	6881
UMC First Aid	05/10/17	6881
UMC First Aid	06/08/17	7000

[Ashish_Mathur]

Hi @ddeutschman,

What result are you expecting on the pasted dataset?

[ddeutschman]

Here are the expected results.

1.  Job 6262 has been closed.  I am filtering on Open jobs only in the table udJob.   So there isn't a row in udJob for it.

2.  Same is true for Job 6265.  It is closed so no row is found in udJob.

3.  For Job 6623, there are two events in the table UMC Safety Incident Log where the column UMC Status = Recordable.  The value to use for the custom column Last Recordable Date in the table udJob is the latest Date of Incident (Max) 2017-06-16

4.  For job 6881, there is one event in the table UMC Safety Incident Log where the column UMC Status = Recordable.  The value to use for the custom column Last Recordable Date in udJob is the Date of Incident: 2017-04-21

5.  For job 7000, there isn't an event where the column UMC Status = Recordable.  So the date to use for the custom column Date Last Recordable in the table udJob is the column StartDate from the table udJob.  That date is 2017-01-23

If there are no entries in the table UMC Safety Incident Log at all for a Job, then the result would be the same as example 5, the date used is the column StartDate from the table udJob.

[ddeutschman]

Any further ideas?  If you could help me with the syntax for a function for determining the set of UMC Safety Incident Log rows that are related to the udJob row that is being evaluated, I think I could take it from there.  If there was a "Recordable" event, I am setting a Custom Column to 1.  That is working.  So I could perform a sum on the set of related rows and if > 0, then use the Max Incident Date for that related data set, otherwise set the value to the StartDate in udJob.

One twist to this is that there may be a NULL set of related rows if a Recordable event has not occurred.