Forum Discussion
Return value based on Name and criteria
Hi DominykasPoc ,
Here some steps that I want to share, you can check them if they suitable for your requirement.
Here is my test data:Create two columns
Min DateDiff = VAR Datediff_from = ABS(DATEDIFF('Table'[From],TODAY(),DAY)) VAR Datediff_to = ABS(DATEDIFF('Table'[To],TODAY(),DAY)) RETURN IF( Datediff_from < Datediff_to, Datediff_from, Datediff_to )Status Today = VAR minDatediff = CALCULATE( MIN('Table'[Min DateDiff]), ALLEXCEPT( 'Table', 'Table'[NameSurname] ) ) RETURN CALCULATE( MAX('Table'[Status]), FILTER( 'Table', 'Table'[Min DateDiff] = minDatediff ) )Final output
Best regards,
Albert HeIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi DominykasPoc ,
Here some steps that I want to share, you can check them if they suitable for your requirement.
Here is my test data:
Create two columns
Min DateDiff =
VAR Datediff_from = ABS(DATEDIFF('Table'[From],TODAY(),DAY))
VAR Datediff_to = ABS(DATEDIFF('Table'[To],TODAY(),DAY))
RETURN
IF(
Datediff_from < Datediff_to,
Datediff_from,
Datediff_to
)Status Today =
VAR minDatediff =
CALCULATE(
MIN('Table'[Min DateDiff]),
ALLEXCEPT(
'Table',
'Table'[NameSurname]
)
)
RETURN
CALCULATE(
MAX('Table'[Status]),
FILTER(
'Table',
'Table'[Min DateDiff] = minDatediff
)
)Final output
Best regards,
Albert He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Okay, seems like I have found a way.
What I have done:
Identified if the date "to" is less than TODAY, then write "99999" , if date "to" is more than TODAY(), then we are looking for difference between "FROM" and TODAY. Then for Status_Today we are looking for Status which has the lowest value "FROM".