cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
bmassino Frequent Visitor
Frequent Visitor

Calculate number of months since ship date and action date.

Hi all,

 

So, I am trying to calculate the difference between two different date columns in two separate tables.

Essentially, I have 2 tables that look like this (sorry for the poor screenshot lol)

 

Example.png

 

So, I am trying to calculate the difference between these dates in terms of months. But I cannot figure out how to actually calculate it using DATEDIFF. I can't have a relationship between the two dates because they are completely different. I would think to create a new column within the shipped date table and use DATEDIFF(Shipped Date[Shipped Date], Action Date[List], MONTH) but it refuses to accept it, saying it cannot find Action Date[List] or that there needs to be a relationship between them. 

 

I would appreciate any help!

 

1 ACCEPTED SOLUTION

Accepted Solutions
TeigeGao New Contributor
New Contributor

Re: Calculate number of months since ship date and action date.

Hi @bmassino ,

In your scenario, it is not supported to create a relationship between two tables because they don't have a same column to create relationship, at least you need to have a id column or other.

If you want to calculate the DATEDIFF row by row, you can create an index column for two tables like below:

L57pnoNipF.gif

Then we can create a relationship between these two index column, after that we can create a calculated column using the following DAX query:

Column = CALCULATE(DATEDIFF(MIN(Table1[Shipped Date]),MIN(Table2[Action Date]),MONTH))

The result will like below:

PBIDesktop_verc93sKSL.png

Best Regards,

Teige

3 REPLIES 3
Nick_M New Contributor
New Contributor

Re: Calculate number of months since ship date and action date.

in your shipped date table, what date from the other table would you be looking to subtract? Seems like there should be a few more columns to each of these tables.

TeigeGao New Contributor
New Contributor

Re: Calculate number of months since ship date and action date.

Hi @bmassino ,

In your scenario, it is not supported to create a relationship between two tables because they don't have a same column to create relationship, at least you need to have a id column or other.

If you want to calculate the DATEDIFF row by row, you can create an index column for two tables like below:

L57pnoNipF.gif

Then we can create a relationship between these two index column, after that we can create a calculated column using the following DAX query:

Column = CALCULATE(DATEDIFF(MIN(Table1[Shipped Date]),MIN(Table2[Action Date]),MONTH))

The result will like below:

PBIDesktop_verc93sKSL.png

Best Regards,

Teige

bmassino Frequent Visitor
Frequent Visitor

Re: Calculate number of months since ship date and action date.

That makes more sense. I have a string for each customer, am I not able to relate that between the two?