Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only

View My Drafts

Back to discussions
Expand all | Collapse all

Check if record is in Previous Index Number.

Jump to Best Answer

  • 1.  Check if record is in Previous Index Number.

    Posted May 06, 2022 06:14 AM
    I'm trying to create a column to show if a record is new (based on previous date or index). I imagine this isn't too complicated but i haven't been able to find a way to do this. I've started off with columns "Date" and "Name". Then added an additional column "Date Index"

    The aim is for column "Status" to show 'New' if the name is not in the previous Date Index

    Date                       Date Index           Name                              Status
    01/05/2022 1 Bill
    01/05/2022 1 Mary
    02/05/2022 2 Bill
    02/05/2022 2 Mary
    02/05/2022 2 John New
    03/05/2022 3 Bill
    03/05/2022 3 Mary
    03/05/2022 3 John
    03/05/2022 3 Sarah New
    06/05/2022 4 Mary
    06/05/2022 4 Jane New
    06/05/2022 4 Peter New


    Any help would be greatly appreciated.

    Thanks


  • 2.  RE: Check if record is in Previous Index Number.
    Best Answer

    Posted May 07, 2022 12:04 PM
      |   view attached
    Hi Dom,
    You need calculated column, not measure
    Status =
    Var NameSearch = LOOKUPVALUE(DomW[Name],DomW[Date Index],[Date Index]-1,DomW[Name],DomW[Name],"")
    Return
    IF(DomW[Name] = NameSearch,"","New")

    Regards

    ------------------------------
    Adam Artur Boltryk
    Business Analyst
    ------------------------------

    Attachment(s)

    pbix
    DomW.pbix   37 KB 1 version
    Original Message