- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

# How to add date field from Parent table to child table and calculate date difference in Days?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-12-2018 05:42 AM

Hii,

I have a table name **SalesTable** having fields **SalesId**, **CreationDate** etc.

I have a child table name **fSalesTrans** having **SalesId**, **InvoiceDate** etc.

Relation field id SalesId.

1) How to add CreationDate field of SalesTable to fSalesTrans table? Measure or New Column?

2) How to get the Date Difference in Days between fSalesTrans InvoiceDate and fSalesTrans new Measure / or Column added from SalesTable?

Thanks,

Solved! Go to Solution.

Accepted Solutions

## Re: How to add date field from Parent table to child table and calculate date difference in Days?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-12-2018 07:30 AM

I recommend you do this in Power Query as it will make the DAX model more efficient. You would merge the two tables basedon the SalesID column, expand the Invoice Date column, then use the following forumla in a new column:

Duration.Days([InvoiceDate] - [CreationDate])

You can also do this by selecting both the InvoiceDate and CreationDate columns, then go to the Add Columns tab, move over to the Date section and pick Subtract Days and it will add the above formula for you.

If you really need to do this in te data model tables themselves, you would use the RELATED function. Assuming SalesTable is related to fSalesTrans in a one-to-many relationship, in fSalesTrans, add this formula:

Date Difference = fSalesTrans[InvoiceDate] - RELATED(SalesTable[CreationDate])

See this file for more details.

I put the Power Query example in the SalesTable table, and the RELATED function above in the fSalesTrans. You could work from either direction really, but you'd use RELATEDTABLE if you were on the one side of a one-to-many table. My simple data set is really a one to one, but it should give you an idea how it works.

For a few rows it does't matter if you use DAX or Power Query, but if you have hundreds of thousands or millions of rows, a calculated column is much MUCH more inefficient than doing it in your source data via Power Query, so I always try to do a PQ calculation vs a calculated column.

## Re: How to add date field from Parent table to child table and calculate date difference in Days?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-13-2018 10:28 AM

I'm not sure exactly what you are asking. Redownload the file I linked to above. I added two functions:

This counts the rows in the transaction table

Total Rows = COUNTROWS(fSalesTrans)

This divides the average days by the total number of rows.

Average Day Difference = DIVIDE( SUM(fSalesTrans[Date Difference]), [Total Rows], 0 )

I put the value on a card. I'm not sure I'm answering your question though as I don't understand how you would put an average value on a pie chart. A pie chart is best for comparing 2 items. If you put one on there, it will just be a whole pie.

## Re: How to add date field from Parent table to child table and calculate date difference in Days?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-14-2018 05:00 PM

If it is blank, use

if [Invoice Ack Date] = "" then blahblahblah

If it is 1/1/1900, that means it came up as 0, not blank. In either event, try

if [Invoice Ack Date] = #date(1900,1,1) then blahblahblah

Your code is comparing it to a text string "1/1/1900" and it isn't that.

#date(yyyy,mm,dd) is the same thing as Date(year,month,day) in Excel formulas. I have no idea why it has a # in front of it. There are half a dozen functions in Power Query like that.

All Replies

## Re: How to add date field from Parent table to child table and calculate date difference in Days?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-12-2018 07:30 AM

I recommend you do this in Power Query as it will make the DAX model more efficient. You would merge the two tables basedon the SalesID column, expand the Invoice Date column, then use the following forumla in a new column:

Duration.Days([InvoiceDate] - [CreationDate])

You can also do this by selecting both the InvoiceDate and CreationDate columns, then go to the Add Columns tab, move over to the Date section and pick Subtract Days and it will add the above formula for you.

If you really need to do this in te data model tables themselves, you would use the RELATED function. Assuming SalesTable is related to fSalesTrans in a one-to-many relationship, in fSalesTrans, add this formula:

Date Difference = fSalesTrans[InvoiceDate] - RELATED(SalesTable[CreationDate])

See this file for more details.

I put the Power Query example in the SalesTable table, and the RELATED function above in the fSalesTrans. You could work from either direction really, but you'd use RELATEDTABLE if you were on the one side of a one-to-many table. My simple data set is really a one to one, but it should give you an idea how it works.

For a few rows it does't matter if you use DAX or Power Query, but if you have hundreds of thousands or millions of rows, a calculated column is much MUCH more inefficient than doing it in your source data via Power Query, so I always try to do a PQ calculation vs a calculated column.

## Re: How to add date field from Parent table to child table and calculate date difference in Days?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-13-2018 12:31 AM

@edhans Thank you for your great reply and it solved my problem. I will mark it answered.

From the same reference, I have one more question.

Now I want to Calculate the Average of Difference in Days.

e.g. as in the following pic.

Total Diff in Days = 3360033

Total rows in this table = 15600

How to calculate the Average in Diff. In Days?

Thanks,

## Re: How to add date field from Parent table to child table and calculate date difference in Days?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-13-2018 01:34 AM

I want to calculate Average of total Difference in Days value automatically. And will create a pi chart? How to achieve it?

Thanks.

## Re: How to add date field from Parent table to child table and calculate date difference in Days?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-13-2018 10:28 AM

I'm not sure exactly what you are asking. Redownload the file I linked to above. I added two functions:

This counts the rows in the transaction table

Total Rows = COUNTROWS(fSalesTrans)

This divides the average days by the total number of rows.

Average Day Difference = DIVIDE( SUM(fSalesTrans[Date Difference]), [Total Rows], 0 )

I put the value on a card. I'm not sure I'm answering your question though as I don't understand how you would put an average value on a pie chart. A pie chart is best for comparing 2 items. If you put one on there, it will just be a whole pie.

## Re: How to add date field from Parent table to child table and calculate date difference in Days?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-14-2018 01:41 AM

@edhans Thanks for your perfect answers. From the same reference I have one more question.

In Power Query Editor I am trying to write following formula, Invoice Ack Date field in fSalesTrans table. Most of the times, this field in empty in SQL Server Database table, but when it comes to Power BI it turns to 1/1/1900.

So I am writing following IF condition but it is still returning some negative integer values like -42365.

Please guide how to handel it.

if [Invoice Ack Date] = "1/1/1900" then 0 else Duration.Days([Invoice Ack Date] - [Invoice Date])

Thanks

## Re: How to add date field from Parent table to child table and calculate date difference in Days?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-14-2018 05:00 PM

If it is blank, use

if [Invoice Ack Date] = "" then blahblahblah

If it is 1/1/1900, that means it came up as 0, not blank. In either event, try

if [Invoice Ack Date] = #date(1900,1,1) then blahblahblah

Your code is comparing it to a text string "1/1/1900" and it isn't that.

#date(yyyy,mm,dd) is the same thing as Date(year,month,day) in Excel formulas. I have no idea why it has a # in front of it. There are half a dozen functions in Power Query like that.

## Re: How to add date field from Parent table to child table and calculate date difference in Days?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-15-2018 02:25 AM

@edhans Thank you and you have the best answers.

## Re: How to add date field from Parent table to child table and calculate date difference in Days?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-15-2018 11:53 AM

Glad to help.