cancel
Showing results for
Did you mean:
Frequent Visitor

## Fill a column based on a value in another column

Hi,

I need to solve a selecting issue, didn't find something for weeks now.

I have a list of items (not unique) with entries for several dates (Timestamp).

Each item has a separate due date.

I want to find the last row and then the unique value for "Team" just before the due date has arrived.

 Item Number Deadline Timestamp Team 10612489 28.02.2019 09:11:00 27.02.2019 10:44:34 Team 1 10612489 28.02.2019 09:11:00 27.02.2019 10:46:30 Team 1 10612489 28.02.2019 09:11:00 28.02.2019 08:48:58 Team 2 10612489 28.02.2019 09:11:00 28.02.2019 12:45:30 Team 1 10612489 28.02.2019 09:11:00 28.02.2019 22:36:25 Team 1 10612489 28.02.2019 09:11:00 01.03.2019 08:27:24 Team 1 10612489 28.02.2019 09:11:00 01.03.2019 19:17:47 Team 2 10616947 11.03.2019 11:48:00 08.03.2019 09:38:57 Team 3 10616947 11.03.2019 11:48:00 08.03.2019 09:52:07 Team 4 10616947 11.03.2019 11:48:00 08.03.2019 09:52:32 Team 1 10616947 11.03.2019 11:48:00 08.03.2019 17:41:37 Team 1 10616947 11.03.2019 11:48:00 11.03.2019 22:24:09 Team 1 10616947 11.03.2019 11:48:00 12.03.2019 08:54:38 Team 3 10617049 08.03.2019 15:49:00 08.03.2019 11:49:54 Team 1 10617049 08.03.2019 15:49:00 08.03.2019 13:25:26 Team 1 10617049 08.03.2019 15:49:00 08.03.2019 13:26:06 Team 5 10617049 08.03.2019 15:49:00 11.03.2019 09:43:11 Team 5

Solution would be then

 Item Number Team 10612489 Team 2 10616947 Team 1 10617049 Team 5

I did not manage to summarize that or to use calculate, maybe I am not experienced enough.

Any help is greatly appreciated!

Jack

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
New Contributor

## Re: Fill a column based on a value in another column

Hi @Jack42
I did it in 3 stages.

2. Find the max date  -

MAXDATE = CALCULATE(MAX(Table1[Timestamp]),
FILTER(Table1,Table1[Item Number]=EARLIER(Table1[Item Number])),
)

3. Find the goal record - Goal Record = IF(Table1[MAXDATE] = Table1[Timestamp], 1,0)

Then filter by Goal Record = 1

4 REPLIES 4
Super User

## Re: Fill a column based on a value in another column

If you want to create this as a calculated column you could use an expression like the following

```Last Team =
VAR _currentItem = Table1[Item Number]
VAR _lastTimeBeforeDeadline = MAXX(FILTER(table1, Table1[Item Number] = _currentItem && Table1[Timestamp] < Table1[Deadline]),  Table1[Timestamp])
Highlighted
New Contributor

## Re: Fill a column based on a value in another column

Hi @Jack42
I did it in 3 stages.

2. Find the max date  -

MAXDATE = CALCULATE(MAX(Table1[Timestamp]),
FILTER(Table1,Table1[Item Number]=EARLIER(Table1[Item Number])),
)

3. Find the goal record - Goal Record = IF(Table1[MAXDATE] = Table1[Timestamp], 1,0)

Then filter by Goal Record = 1

Frequent Visitor

## Re: Fill a column based on a value in another column

Dear AClerk,

brilliant, works very good. I learned a lot by that... many thanks!

I only needed to replace the kommata in the two „if“ statements with semicoli.

just in case s.o. finds this post and reuses it...

Cheers, Jack 👍🏻

Frequent Visitor

## Re: Fill a column based on a value in another column

Dear d_gosbell,

many thanks for your input... unfortunately I could not bring that statement to life - I do something wrong here... Anyhow, the other answer I got was successful, so you do not need to spend more effort in here.

Please again, many thanks for your effort and time! Have a nice day!

Jack 🖖

Announcements

#### Challenge: Can You Solve These?

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

#### Community News & Announcements

Get your latest community news and announcements.

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

#### Win Power BI Swag with Community Kudopalooza!

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

Top Kudoed Authors
Users Online
Currently online: 141 members 1,661 guests
Recent signins: