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.

v-xicai's avatar
v-xicai
Icon for Community Support rankCommunity Support
7 years ago

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/EZs99glFqZpJu5u0s3GCAusBjfRH8YaO0mtrf9kKi00oTg?e=ojN3UL

 

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.

    • v-xicai's avatar
      v-xicai
      Icon for Community Support rankCommunity Support
      7 years ago

      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