Forum Discussion

ddeutschman's avatar
ddeutschman
Icon for Helper I rankHelper I
8 years ago

How calculate a column based on

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...
Need Help
Show and Tell
v-ljerr-msft's avatar
v-ljerr-msft
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

Hi ddeutschman,

 

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

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's avatar
    ddeutschman
    Icon for Helper I rankHelper I
    8 years ago

    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's avatar
      ddeutschman
      Icon for Helper I rankHelper I
      8 years ago

      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's avatar
        v-ljerr-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        8 years ago

        Hi ddeutschman,

         

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

        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