How do you pull the last date in a DATEDIFF calculation?

[PBIUWO]

Hi, 

I have a table that looks like below:

My question will be 2 parts.

I originally had a DATEDIFF, DAX between Order Date and Ship Date. Then created a AVG for the DATEDIFF, and this was based on the number of lines. However, I would like to only see the AVG DATEDIFF for Unique PO #. 

In the example, it would be the AVG of 2 PO #, instead of the 5 lines.

Secondly, how do I create a DATEDIFF for the unique PO #, to only use the latest ship date. 

Example, for PO #123, it would be DATEDIFF(1/1/2019,1/3/2019) and for PO#124, DATEDIFF(2/2/2019,2/6/2019)

Thanks,

[v-xicai]

Hi @PBIUWO ,

You can create measures like DAX below.

Average DATEDIFF = SUMX(Table1,DATEDIFF(Table1[Order Date],Table1[Ship Date],DAY))/DISTINCTCOUNT(Table1[PO #])

DateDiff = CALCULATE(DATEDIFF(MAX(Table1[Order Date]),MAX(Table1[Ship Date]),DAY),FILTER(Table1,Table1[Ship Date]=LASTDATE(Table1[Ship Date])&&Table1[PO #]=MAX(Table1[PO #])))

DateDiff for latest ship date =

VAR _table = SUMMARIZE(Table1, Table1[PO #],"_Value",[DateDiff])

RETURN

IF(HASONEVALUE(Table1[PO #]),[DateDiff],SUMX(_table,[_Value]))

Here is my test pbix: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EZs99glFqZpJu5u0s3...

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PBIUWO]

Hi @v-xicai ,

Really appreciate this! 

However, on the DATEDIFF measure I am getting this error. As result, latest ship measure is also an erorr. 

"Error Message:
MdxScript(Model) (4, 154) Calculation error in measure 'YTD'[DateDiff]: A date column containing duplicate dates was specified in the call to function 'LASTDATE'. This is not supported.

"

*YTD is the name of your Table1 

[v-xicai]

Hi  @PBIUWO ,

Does that make sense? If so, kindly mark my answer as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

Best regards

Amy

[v-xicai · Accepted solution]

Hi,

You can create measure Latest Ship Date first, then create measure DateDiff .

Latest Ship Date = CALCULATE(MAX(Table1[Ship Date]),FILTER(ALL(Table1),Table1[PO #]=MAX(Table1[PO #])))

DateDiff = CALCULATE(DATEDIFF(MAX(Table1[Order Date]),[Latest Ship Date],DAY),FILTER(Table1,Table1[PO #]=MAX(Table1[PO #])))

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.