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 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,

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.

4 Replies

  • 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.