Calculated table/column based on lookup of column with repeat values

[m_fisher]

I am a novice Power BI user trying to create a calculated table or column based off of other columns within a single table. The original table (call it "ProductCheckIn") contains a column of product serial numbers ("SerialNumber") which have numerous rows per serial number, each row timestamped (noted in "timestamp" column) for the point in time when it was created. The calculated table I want to create would return the following: 

* Column1 = "SerialNumber" with a row for each distinct serial number

* Column2 = "StartDate" which populate with the minimum or earliest timestamp for a given serial number

* Column3 = "EndDate" which would be the maximum or latest timestamp for a given serial number

* Column4 = "DeploymentAge" which would calculate the difference between Column3 and Column2 above. 

Any help pointing me in the right direction would be greatly appreciated!

[BILASolution · Accepted solution]

Hi @m_fisher

I created a sample table with ficticious data

The next dax formula create a calculated table you are looking for...

Resume = 

SUMMARIZE
(
    ProductCheckIn;
    ProductCheckIn[SerialNumber];
    "StartDate";MIN(ProductCheckIn[TimeStamp]);
    "EndDate";MAX(ProductCheckIn[TimeStamp]);
    "DeploymentAge";DATEDIFF(MIN(ProductCheckIn[TimeStamp]);MAX(ProductCheckIn[TimeStamp]);DAY)
) 

And the result is....

I hope it helps

BILASolution

[m_fisher]

Hi @BILASolution thank you so much for the quick response! This was really helpful, and worked for me with just a few small changes. Power BI did not like the semicolons so I had to replace them with commas, but it worked beautifully! Thanks again.