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

     

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