Forum Discussion
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 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?
9 Replies
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
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.
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?
