topic Re: How to merge 2 columns ( start date and end date ) with column of time using on if statement in DAX Commands and Tips

How to merge 2 columns ( start date and end date ) with column of time using on if statement

Anonymous — Thu, 10 Feb 2022 08:59:59 GMT

Hello,

I m having a trouble with merging dates and time based on conditions : i have 2 dates :

Start date and end dates, and i have many columns with time Format , i want to merge the columns using those conditions :

If ( Column Time <= (00:00:00) and start date = end date then [Start Date]& " "&[TimeColumn] else [end date]& " "& [TimeColumn]

this is not working neither on calculated colmun nor on dax measure

my final goal is to obtain for each timecolumn a merged column with the appropriate date (end date ==>if the time is equal or over 00:00:00 other wise merge with the start date!

If u have any ideas, i ve been blocked on this a week noww ! 😞

Re: How to merge 2 columns ( start date and end date ) with column of time using on if statement

amitchandak — Thu, 10 Feb 2022 09:41:56 GMT

Anonymous ,

If you need in DAx

If ( [Column Time] <= time(0,0,0) && [start date] = [end date] , [Start Date]+ [TimeColumn] , [end date]+ [TimeColumn] )

in power Query

If [Column Time] <= #time(0,0,0) and [start date] = [end date] then [Start Date]+ [TimeColumn] else [end date]+ [TimeColumn]

Re: How to merge 2 columns ( start date and end date ) with column of time using on if statement

Anonymous — Thu, 10 Feb 2022 09:54:45 GMT

thank you, i just tried, i cannot apply + on date and time formats on powerquery

for the other values i got "null values" on my custom column

i dont know if there is another way to put condition on times and date in order to calculate the right duration because without merging the dates ( end and start) and times, the duration of 2 task between 23 PM and 1 AM is 22h instead of 2h ! 😞

Re: How to merge 2 columns ( start date and end date ) with column of time using on if statement

Anonymous — Thu, 10 Feb 2022 10:04:02 GMT

on dax its working but i get weird dates instead of my dates values :

Re: How to merge 2 columns ( start date and end date ) with column of time using on if statement

Anonymous — Thu, 10 Feb 2022 10:14:57 GMT

hello,

i just tested another case and the condition is not working correctly i think : for this date since we re at 23h PM he should choose the date 06/01 and not the 07/01

do u have an idea please how can i add more condition for midnight value !

thanks in advance ! 🙂

Re: How to merge 2 columns ( start date and end date ) with column of time using on if statement

Anonymous — Thu, 10 Feb 2022 11:28:50 GMT

Instead of '+', try using '&'

Re: How to merge 2 columns ( start date and end date ) with column of time using on if statement

Anonymous — Fri, 18 Feb 2022 07:39:58 GMT

Hi Anonymous ,

Please check the formula.

Column = IF(TableA[Column Time]<TIME(00,00,00)&&TableA[start date]=TableA[end date],TableA[start date]+TableA[Column Time],TableA[end date]+TableA[Column Time])

Best Regards,

Jay