Calculating Change in Units on a day to day basis

[sean111]

I have a table with the following headers; Company Code, Holder ID, Units, Date

This table receives ingestions on a daily basis and includes a snapshot of the holders of shares of a company. So on a particular day (1/5/23), Holder X will hold 10 shares of Company A. 

Company Code, Holder ID, Units, Date

A, X, 10, 1/5/23

I am trying to add another column to this named Change in Units. When there is a change in the number of units held of a company by an investor from the previous day, I want to record the change in the number of units

For example:

Company Code, Holder ID, Units, Date, Change in Units

A, X, 10, 1/5/23, null

A, X, 10, 2/5/23, null

A, X, 11, 3/5/23, 1

A, X, 10, 4/5/23, -1

On an additional note, when a holder sells all of their holdings, they will not be included in the next snapshot of the holders of the company. I need to indentify when this occurs and create a new row to include the change in the units for the given date. 

[lbendlin]

Please provide sample data that covers your issue or question completely.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[sean111]

I've attached the link to the sample data here https://docs.google.com/spreadsheets/d/1yRypaXtaQ9lqeLBQQhrdGaszis0dvS3XrQFDsbm-BLE/edit?usp=sharing 

Ideally, I am looking for a new table which only shows the trade dates and change in units. 

Another note, is that the data is only received for weekdays, nothing is received for weekends