Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
carol_mar
Helper I
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 numbernamephone number
aaa1  
aaa2  
aaan  

Table 2: 

serial numberpayment date
aaa11/4/20
aaa25/5/20
aaa13/5/20
aaa12/6/20
aaa24/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 numberDifference of date [datediff(today, first payment date)]
aaa1200 days
aaa2120 days
aaa350 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! 

date Difference between dates Calculation @Tapemeasure Getting aggregation type of a measure issue 

1 ACCEPTED SOLUTION

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 )

table1table1table2table2table3table3

Attached a sample file in the below, hopes to help you.

 

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.

View solution in original post

8 REPLIES 8
v-yingjl
Community Support
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
        )
)

re.png

Attached a sample file in the below, hopes to help you.

 

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.

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!

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,

First, thank you for your reply.

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

Table 1

Customer nameCustomer SurnameSerial NumberAccount Number
aaazzz123a1a1
bbbvvv456b2b2
cccuuu789

c3c3

Table 2:

Serial NumberCustomer namephone numberstatus
aaa
bbb
ccc

Table 3:

Account NumberGenderYearslong
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 🙂

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 )

table1table1table2table2table3table3

Attached a sample file in the below, hopes to help you.

 

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.

amitchandak
Super User
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)

Hi! Thank you so much for your reply. 

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? 

@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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.