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

View solution in original post

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

View solution in original post

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

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 43 members 1,931 guests
Please welcome our newest community members: