time difference

[Anonymous]

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 · Accepted solution]

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]

This solved my problem: https://community.powerbi.com/t5/Desktop/Datediff-start-date-cannot-be-greater-than-the-end-date/td-...

Thank you all.

[Sujit_Thakur]

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

[Sujit_Thakur · Accepted solution]

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 

[harshnathani]

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

[harshnathani · Accepted solution]

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