topic Re: time difference in DAX Commands and Tips

time difference

Anonymous — Sun, 16 Aug 2020 15:35:33 GMT

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?

Re: time difference

Anonymous — Sun, 16 Aug 2020 15:49:57 GMT

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

Re: time difference

Sujit_Thakur — Sun, 16 Aug 2020 16:03:59 GMT

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

Re: time difference

Anonymous — Sun, 16 Aug 2020 15:44:14 GMT

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

Re: time difference

Anonymous — Sun, 16 Aug 2020 16:09:35 GMT

This solved my problem: https://community.powerbi.com/t5/Desktop/Datediff-start-date-cannot-be-greater-than-the-end-date/td-p/108512#:~:text=Re%3A%20Datediff%20start%20date%20cannot%20be%20greater%20than%20the%20end%20date&text=Test%20your%20dates%20if%20the,function%20the%20other%20way%20round.

Thank you all.

Re: time difference

Sujit_Thakur — Sun, 16 Aug 2020 16:14:11 GMT

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