Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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 ...
Need Help
Show and Tell
Tips and Tricks
Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
3 years ago

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's avatar
Anonymous
Not applicable
3 years ago

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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    3 years ago

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