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

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)

Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau 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

@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

Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!

Announcements

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!