Return value based on Name and criteria

[DominykasPoc]

Dear community,

I am asking for help to get the right formula for the solution below (example made in Excel, but formula solution is needed in PBI):

Table1 (left) - Contains a person and his activity within two dates and status.
Table2 (right) - A table of unique names and StatusToday.

I need to get a formula which would return unique person's status of today. 
Need to look if TODAY's date falls in between of "FROM" and "TO", if yes - return "Status". If no - return status of the future "FROM" date which is the closest from TODAY's date.

For example: Tom Tom is has nothing within TODAY date, so in this case it should return his status as "Sick", since TODAY is 07/22 and the closest to TODAY's date for Tom is 07/25.

Much appreciated.

[Anonymous · Accepted solution]

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

[DominykasPoc]

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".

[DominykasPoc]

Hi @Anonymous ,

I have spotted a scenario which does not fully completes the result.

So as we can see the minimum DateDiff is 7, however this status has ended and the new status which starts 08/03 is present. In this case if all other status are over I would like for Status_Today to show the next status (DateDiff = 11, which means "Suplanuota" (Planned)). How should we make this logic work?

[DominykasPoc]

Hi Albert,

Very smart way of identifying the minimum difference, great idea. 
I was struggling at first to find whats not working on my real data file. 

I did changed abit the calculation of Status_Today of the RETURN phase. Inserted the fix here so in case someone will be facing the same. 

Albert, maybe you could comment why did this small change made an effect of the result? Because with your example I was getting wrong results (formula worked, but the results were wrong, in some cases I was getting "status" which wasnt present in the data for that particular Name/Surname at all).

Much appreciated,
Dominykas