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

[ranaanees]

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,

[edhans · Accepted solution]

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 · Accepted solution]

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. 

[edhans · Accepted solution]

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]

@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]

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

Thanks.

[edhans · Accepted solution]

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]

@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

[edhans · Accepted solution]

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]

@edhans Thank you and you have the best answers. 

[edhans]

Glad to help.