Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

time difference

Hello.

 

I have 2 columns with the following data: picking date and faturation date. I want to know the time difference between these two processes but in some rows I dont have the picking date and in some rows I dont have the faturation date. So, I only want the time difference between orders that have the 2 fields and if I dont have at least one field in one column, I want a message like "without dates". 

 

Anyone can help?

  • Sujit_Thakur's avatar
    Sujit_Thakur
    6 years ago

    Dear Anonymous 

    You can use this measure 

    This will work the best and also you will be able to filter by field using any drop down or slicer

    Date Diff = CALCULATE(IF(ISBLANK(SUMX(Sheet1,Sheet1[Picking Date ])) || ISBLANK(SUMX(Sheet1,Sheet1[faturation Date])),"no dATE" ,SUMX(Sheet1,DATEDIFF(Sheet1[Picking Date ] , Sheet1[faturation Date],DAY))))
     

     

    This will do the job 

    Just use your table name in the place where i have used Sheet1

     

     

    I would appreciate a kudos and if this helped please accept as solution so our other brothers and sisters can get help from it 👍

     

     

    If any more further doubt please let me know .

    Regards 

    Thakur Sujit 

  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Anonymous ,

     

    Try creating a Calculated Column

     

    New Column =
IF (
    'Table'[picking date]
        <> BLANK ()
        && 'Table'[faturation date]
            <> BLANK (),
    DATEDIFF (
        'Table'[picking date],
        'Table'[faturation date],
        DAY
    ),
    99999
)

     

    If any of the dates are not present it will return 99999. YOu cannot have text and number values in the same column.

     

    Regards,

    Harsh Nathani

     

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous ,

     

    Try creating a Calculated Column

     

     

     

     

    New Column =
IF (
    'Table'[picking date]
        <> BLANK ()
        && 'Table'[faturation date]
            <> BLANK (),
    DATEDIFF (
        'Table'[picking date],
        'Table'[faturation date],
        DAY
    ),
    99999
)

     

     

     

     

    If any of the dates are not present it will return 99999. YOu cannot have text and number values in the same column.

     

    Incase you need time difference in hour, second, minutes, weeks  replace DAY by them. Refer the below guide

     

    https://dax.guide/datediff/

     

    Regards,

    Harsh Nathani

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Anonymous ,

       

      Try creating a Calculated Column

       

      New Column =
IF (
    'Table'[picking date]
        <> BLANK ()
        && 'Table'[faturation date]
            <> BLANK (),
    DATEDIFF (
        'Table'[picking date],
        'Table'[faturation date],
        DAY
    ),
    99999
)

       

      If any of the dates are not present it will return 99999. YOu cannot have text and number values in the same column.

       

      Regards,

      Harsh Nathani

       

  • Sujit_Thakur's avatar
    Sujit_Thakur
    Solution Sage
    6 years ago

    Dear Anonymous 

    You can use this measure 

    This will work the best and also you will be able to filter by field using any drop down or slicer

    Date Diff = CALCULATE(IF(ISBLANK(SUMX(Sheet1,Sheet1[Picking Date ])) || ISBLANK(SUMX(Sheet1,Sheet1[faturation Date])),"no dATE" ,SUMX(Sheet1,DATEDIFF(Sheet1[Picking Date ] , Sheet1[faturation Date],DAY))))
     

     

    This will do the job 

    Just use your table name in the place where i have used Sheet1

     

     

    I would appreciate a kudos and if this helped please accept as solution so our other brothers and sisters can get help from it 👍

     

     

    If any more further doubt please let me know .

    Regards 

    Thakur Sujit 

    • Sujit_Thakur's avatar
      Sujit_Thakur
      Solution Sage
      6 years ago

      Dear Anonymous ,
      You can please atleast appreciate the solution authors by giving kudos  .
      Both of above post are also correct solution , you can accept both of them as solution if you want.
      Regards ,

      thakur sujit