cancel
Showing results for
Did you mean:
Helper I

## for each raw value of the column table calculate difference in dates that are in another table

Hi guys!

I have 2 tables and i need to create e 3rd one.

the first table is Table 1:

 Serial number name phone number aaa1 aaa2 aaan

Table 2:

 serial number payment date aaa1 1/4/20 aaa2 5/5/20 aaa1 3/5/20 aaa1 2/6/20 aaa2 4/6/20 aaa2 25/8/20 aaan ...

I need to create a table which contains a column with all the serial numbers as unique values for each raw and another column with the difference in number of days between today and the FIRST payment date they made for instance for aaa1 the difference would be 11/01/2021-1/4/2020=(guess)200 days

Table 3:

 Serial number Difference of date [datediff(today, first payment date)] aaa1 200 days aaa2 120 days aaa3 50 days aaan n days

I'm not sure if I should create a calculated column or a measure because when I create a column close to the serial number column it looks like it doesn't give me the difference of days between the first payment related to that specific serial number.

Does anyone know how to help me?

thanks!

1 ACCEPTED SOLUTION
Community Support

Hi @carol_mar ,

Based on your description, you can use LOOKUPVALUE()  to create calculated columns in table 2 which meets conditions from table 1 and table 3:

``````Gender years =
VAR a =
LOOKUPVALUE (
'Table 1'[Account Number],
'Table 1'[Customer name], 'Table 2'[Serial Number]
)
RETURN
LOOKUPVALUE ( 'Table 3'[GenderYears], 'Table 3'[Account Number], a )``````
``````long =
VAR a =
LOOKUPVALUE (
'Table 1'[Account Number],
'Table 1'[Customer name], 'Table 2'[Serial Number]
)
RETURN
LOOKUPVALUE ( 'Table 3'[long], 'Table 3'[Account Number], a )``````

table1table2table3

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

8 REPLIES 8
Community Support

Hi @carol_mar ,

Based on your description, you can create this calculated table as Table 3:

``````Table 3 =
SUMMARIZE (
'Table 1',
'Table 1'[Serial number],
"Difference of date",
DATEDIFF (
CALCULATE (
MIN ( 'Table 2'[payment date] ),
FILTER (
ALL ( 'Table 2' ),
'Table 2'[Serial number] = EARLIER ( 'Table 1'[Serial number] )
)
),
TODAY (),
DAY
)
)
``````

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helper I

Thank you so much, I'll try that right now!
I have another question for this table:

I have 3 tables, T1 and T2 have the same column containing "serial number", T2 and T3 have the same column containing "Customer Account Number". I need to have in T3 the column "Serial Number" and each serial number is a unique value corresponding to the "serial number". The number of raws in the tables are all different, T1 is the biggest one while T3 the smallest table. How can I do that? In excel would just be an "index and compare" or a "xlookup". Thank you so so much!

Community Support

Hi @carol_mar ,

Not certain what is your expected output.. Could you please consider provide example tables and expected output which is like your initial post for further discussion?

Best Regards,
Community Support Team _ Yingjie Li

Hi,

Below a more detailed explanation of what I need, I have 3 tables:

Table 1

 Customer name Customer Surname Serial Number Account Number aaa zzz 123 a1a1 bbb vvv 456 b2b2 ccc uuu 789 c3c3

Table 2:

 Serial Number Customer name phone number status aaa bbb ccc

Table 3:

 Account Number Gender Years long a1a1 b2b2 c3c3

I need to add a column in Table 2 that contains for each specific serial number the gender, latitude, and longitude that are in Table 3 but identified by the Account Number.

Is this more clear? Thank you 🙂

Community Support

Hi @carol_mar ,

Based on your description, you can use LOOKUPVALUE()  to create calculated columns in table 2 which meets conditions from table 1 and table 3:

``````Gender years =
VAR a =
LOOKUPVALUE (
'Table 1'[Account Number],
'Table 1'[Customer name], 'Table 2'[Serial Number]
)
RETURN
LOOKUPVALUE ( 'Table 3'[GenderYears], 'Table 3'[Account Number], a )``````
``````long =
VAR a =
LOOKUPVALUE (
'Table 1'[Account Number],
'Table 1'[Customer name], 'Table 2'[Serial Number]
)
RETURN
LOOKUPVALUE ( 'Table 3'[long], 'Table 3'[Account Number], a )``````

table1table2table3

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Super User IV

You can have measure for that

datediff(min(tAb2[paymentdate]), max(tab2[paymentdate]), Day)  wnd analyze that with coommon dimension of serial no or first table if joined.

or a column in tabbed colum = datediff(

minx(filter(tab2, tab2[serial no] = tab1[serial no]), tab2[payment date]),

maxx(filter(tab2, tab2[serial no] = tab1[serial no]), tab2[payment date]), day)

Proud to be a Super User!

Helper I

I did try that (calculated column) and it worked on the table itself (apparently) but the issue I had with it was that when I try to visualize the values on a report table it doesn't show any value in that column that's why I thought it was wrong.  Do you know why?

Super User IV

@carol_mar , Strange. from which table you have used the serial number in this case. There few blank values for mismatch of the serial number.

Can you share a sample pbix after removing sensitive data, it this not working even after having the correct column/s and aggregation

Proud to be a Super User!

Announcements

#### The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

#### Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors