Return The First Date the Staff Failed

[Anonymous]

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?

[Ashish_Mathur]

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.

[Anonymous]

Hi, I tried on my report and it only returns the very first date, which is 1/9/2021 for all rows

[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())