Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
PBIUWO
Helper III
Helper III

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

Hi, 

 

I have a table that looks like below:

Capture.PNG

 

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,

1 ACCEPTED SOLUTION
v-xicai
Community Support
Community Support

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.

View solution in original post

4 REPLIES 4
v-xicai
Community Support
Community Support

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

 

 

3.png

 

 

 

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.

 

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
Community Support
Community Support

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
Community Support
Community Support

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.