Forum Discussion

AntonN's avatar
AntonN
Frequent Visitor
1 year ago
Solved

Custer/Flag Rows before Sale by Sale Date

Hi! In my data I have the first 3 columns from the table below, in which the rows are ordered descending by date. According to this data, the customer can make a few applications until eventually gen...
  • Kedar_Pande's avatar
    Kedar_Pande
    1 year ago

    AntonN 

    New Column:

    Sale ID Distributed =
    VAR CurrentDate = 'YourTable'[Date]
    VAR CurrentType = 'YourTable'[Type]

    RETURN
    IF (
    CurrentType = "Application",
    VAR PreviousSale =
    CALCULATE (
    MAX ( 'YourTable'[Sale ID] ),
    FILTER (
    'YourTable',
    'YourTable'[Date] < CurrentDate &&
    'YourTable'[Type] = "Sale"
    )
    )
    RETURN
    IF (
    ISBLANK(PreviousSale),
    BLANK(),
    PreviousSale
    ),
    'YourTable'[Sale ID]
    )

    Once you have created the calculated column using the above formula, you should see the appropriate Sale ID distributed to the Application rows as required.

     

    💌If this helped, a Kudos 👍 or Solution mark would be great! 🎉
    Cheers,
    Kedar
    Connect on LinkedIn

  • FreemanZ's avatar
    FreemanZ
    1 year ago

    hi AntonN ,

     

    try like:

     

    Fill Down =
    MAXX (
        FILTER (
            'table',
            'table'[category] = EARLIER('table'[category])
                && 'table'[type] = "sale"
                && 'table'[date] >= EARLIER ( 'table'[date] )
                && NOT ( ISBLANK ( 'table'[ID] ) )
        ),
        'table'[ID]
    )
AntonN's avatar
AntonN
Frequent Visitor
1 year ago

Kedar_Pande , thanks a lot! Can this be done per category?