Forum Discussion
Return The First Date the Staff Failed
Hi there,
I want to get the column which returns the first date that staff failed. The table may go like this:
Staff | Date | Passed? | Flag | First Date Failed
A | 1/9/2021 | Failed | Non Repeat | 1/9/2021
A | 1/10/2021 | Failed | Repeat | 1/9/2021
A | 1/11/2021 | Failed | Repeat | 1/9/2021
A | 1/12/2021 | Passed | Non Repeat |
A | 1/1/2022 | Failed | Non Repeat | 1/1/2022
A | 1/2/2022 | Failed | Repeat | 1/1/2022
I tried few workarounds like this
First Date Failed = IF(Table[Flag]="Non Repeat && Table[Passed?]="Failed", Table[Date], BLANK())
But this DAX only returns the one with first time fail date. Can help me with this?
3 Replies
- Ashish_Mathur
Super User
Hi,
Create a calculated column formula to extract the Year
Year = year(Data[Date])
First date failed in year = if(Data[Passed?]="Failed",calculate(min(Data[Date]),filter(Data,Data[Staff]=earlier(Data[Staff])&&Data[Passed?]="Failed")),blank())
Hope this helps.
- Ashish_Mathur
Hi,
Try this
Year = year(Data[Date])
First date failed in year = if(Data[Passed?]="Failed",calculate(min(Data[Date]),filter(Data,Data[Staff]=earlier(Data[Staff])&&Data[Passed?]="Failed"&&Data[Year]=earlier(Data[Year]))),blank())
