Forum Discussion

PBIUWO's avatar
PBIUWO
Icon for Helper III rankHelper III
7 years ago
Solved

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

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...
Need Help
  • v-xicai's avatar
    v-xicai
    7 years ago

    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.

PBIUWO's avatar
PBIUWO
Icon for Helper III rankHelper III
7 years ago

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's avatar
v-xicai
Icon for Community Support rankCommunity Support
7 years ago

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.