cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
m_fisher Frequent Visitor
Frequent Visitor

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

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!

1 ACCEPTED SOLUTION

Accepted Solutions
BILASolution Established Member
Established Member

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

Hi @m_fisher

 

I created a sample table with ficticious data

 

g1.png

 

 

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....

 

g2.png

 

 

I hope it helps

BILASolution

2 REPLIES 2
BILASolution Established Member
Established Member

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

Hi @m_fisher

 

I created a sample table with ficticious data

 

g1.png

 

 

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....

 

g2.png

 

 

I hope it helps

BILASolution

m_fisher Frequent Visitor
Frequent Visitor

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

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.