I want to create column in power bi which gives us previous id based on date column

[Anonymous]

I want to create column in power bi which gives us previous id based on date column.

[TomMartens · Accepted solution]

Hey @Anonymous ,

the below DAX creates a calculated column that stores the previous Shid based on the Date column.

The DAX:

PREVIOUS ID = 
var prevDate = 
    OFFSET(
        -1,
        SUMMARIZE(
            'Table',
            'Table'[Date]
        ),
        ORDERBY( 'Table'[Date] , ASC )
    )
return
SELECTCOLUMNS(
    TOPN(
        1,
        FILTER( 'Table', 'Table'[Date] = prevdate)
    ),
    "id", [Shid]
)

This is my data:

Hopefully, this provides what you are looking for.

Regards,

Tom

[DataNinja777 · Accepted solution]

Hi  @Anonymous , 

To create a column in Power BI that provides the previous ID based on the date column, there are several alternative approaches. However, I noticed that your date column only displays the day within the month, which may not be sufficient as a unique identifier, especially if the dates span across different months.

Due to this limitation, I have utilized the first column as an index column to ensure accuracy.

You can create a calculated column using the following formula:

Previous = VAR CurrentShid = HistoryRecord[Index]
RETURN
    CALCULATE(
        MAX(HistoryRecord[Shid]),
        FILTER(
            HistoryRecord,
            HistoryRecord[Index] < CurrentShid
        )
    )

Alternatively, you can create a measure to use directly in a visualization.

To convert your calculated column Previous into a measure, you need to adjust the logic slightly, as measures operate on the aggregation context rather than row-by-row computations. Here's how you can write the measure equivalent:

Previous (measure) = 
VAR CurrentIndex = MAX(HistoryRecord[Index])  -- Get the current Index in the current context
RETURN
    CALCULATE(
        MAX(HistoryRecord[Shid]),
        FILTER(
            ALL(HistoryRecord),  -- Remove any filters to compare all rows
            HistoryRecord[Index] < CurrentIndex
        )
    )

The resulting output is as shown in the matrix visual below:

 I have attached an example pbix file for your reference.

Best regards,

[TomMartens · Accepted solution]

Hey @Anonymous ,

the below DAX creates a calculated column that stores the previous Shid based on the Date column.

The DAX:

PREVIOUS ID = 
var prevDate = 
    OFFSET(
        -1,
        SUMMARIZE(
            'Table',
            'Table'[Date]
        ),
        ORDERBY( 'Table'[Date] , ASC )
    )
return
SELECTCOLUMNS(
    TOPN(
        1,
        FILTER( 'Table', 'Table'[Date] = prevdate)
    ),
    "id", [Shid]
)

This is my data:

Hopefully, this provides what you are looking for.

Regards,

Tom