- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Integrations with Files and Services
- Mobile Apps
- Developer
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Welcome to the Community
- Community Feedback
- Community Help Blog
- Training and Consulting
- Dashboard in a Day
- EdX Specific Training Discussion Forum

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Re: How to add date field from Parent table to chi...

Topic Options

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

ranaanees

Regular Visitor

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

Report Inappropriate Content

Message 1 of 9

3 ACCEPTED SOLUTIONS

Accepted Solutions

edhans

New Contributor

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

edhans

New Contributor

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

Highlighted

edhans

New Contributor

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

8 REPLIES 8

edhans

New Contributor

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

ranaanees

Regular Visitor

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,

ranaanees

Regular Visitor

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.

edhans

New Contributor

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

ranaanees

Regular Visitor

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

Highlighted

edhans

New Contributor

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

ranaanees

Regular Visitor

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.

edhans

New Contributor

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.