Forum Discussion

DominykasPoc's avatar
DominykasPoc
New Member
2 years ago
Solved

Return value based on Name and criteria

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 ac...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

     



Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
    DominykasPoc
    New Member
    2 years ago

    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

  • DominykasPoc's avatar
    DominykasPoc
    New Member
    2 years ago

    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's avatar
    DominykasPoc
    New Member
    2 years ago

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