creating a column by merging from 2 different column with a condition

[jeffrock]

Hi,

I have a requirement where I need to get the hours based on a single id for a particular day. so I have used LIST to break each id and expanded the LIST to rows which will give me the sequence of dates. My END objective is to get the required hours to get the TREND. in short, I need to create a column with Calc_date which will append time from the "start_date" column of 1st row of an ID from the start_date and time from the "End_Date" column to the last row of an ID. The middle rows will be 00:00:00.

if anyone Understood the situation, please help me out to get the solution as far as can.

ID	Start_date	End_date	Calc_date	REQUIRED COLUMN DATE& TIME	REQUIRED COLUMN Hours
abc	06/06/2020 01:37	09/06/2020 18:00	06/06/2020	06/07/2020 01:37	22.5
abc	06/06/2020 01:37	09/06/2020 18:00	07/06/2020	07/06/2020 00:00	24
abc	06/06/2020 01:37	09/06/2020 18:00	08/06/2020	08/06/2020 00:00	24
abc	06/06/2020 01:37	09/06/2020 18:00	09/06/2020	09/06/2020 18:00	18
xyz	10/10/2022 15:30	13/10/2022 03:00	10/10/2020	10/10/2022 15:30	8.5
xyz	10/10/2022 15:30	13/10/2022 03:00	11/10/2020	11/10/2020 00:00	24
xyz	10/10/2022 15:30	13/10/2022 03:00	12/10/2020	12/10/2020 00:00	24
xyz	10/10/2022 15:30	13/10/2022 03:00	13/10/2020	13/10/2022 03:00	3

Thank you

[v-yanjiang-msft · Accepted solution]

Hi @jeffrock ,

According to your description, here's my solution. Create two calculated columns.

StartTime =
SWITCH (
    'Table'[Date_line],
    'Table'[Date Start], 'Table'[Date Start] + 'Table'[Time In],
    'Table'[Date_line]
)

EndTime =
SWITCH (
    'Table'[Date_line],
    'Table'[Date End], 'Table'[Date End] + 'Table'[Time Out],
    'Table'[Date_line]
)
    + TIME ( 23, 59, 59 )

Get the correct result.

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jeffrock]

Hey @v-yanjiang-msft , Thank you for the Solution.

Is there any way in Dax where we can create a calculated column to convert a column from time format (eg: Time IN Column from above Table) to int

eg: if a column has 18:30:00 in time format it should return 18.3 or 18.5 in int format.

appreciate your Help

Thanks again

[v-yanjiang-msft]

Hi @jeffrock ,

Use this formula:

Column = HOUR('Table'[Column1])&"."&MINUTE('Table'[Column1])

Best Regards,
Community Support Team _ kalyj

[Greg_Deckler]

@jeffrock Add an Index column in Power Query for starters, otherwise there is no way to tell which row is 1st.

[jeffrock]

Hi, @Greg_Deckler I have added but am not sure how to fill the data based on condition. I m attaching the below data, please suggest to me the approach

ID	DateSTart	Date End	Time IN	Time Out	Date_line	Index	StartTime	End Time
abc	01/09/2022	05/09/2022	18:00	15:00	01/09/2022	1	01/09/2022 18:00	01/09/2022 23:59
abc	01/09/2022	05/09/2022	18:00	15:00	02/09/2022	2	02/09/2022 00:00	02/09/2022 23:59
abc	01/09/2022	05/09/2022	18:00	15:00	03/09/2022	3	03/09/2022 00:00	03/09/2022 23:59
abc	01/09/2022	05/09/2022	18:00	15:00	04/09/2022	4	04/09/2022 00:00	04/09/2022 23:59
abc	01/09/2022	05/09/2022	18:00	15:00	05/09/2022	5	05/09/2022 00:00	05/09/2022 15:00
xyz	05/09/2022	08/09/2022	15:00	20:00	05/09/2022	6	05/09/2022 15:00	05/09/2022 23:59
xyz	05/09/2022	08/09/2022	15:00	20:00	06/09/2022	7	06/09/2022 00:00	06/09/2022 23:59
xyz	05/09/2022	08/09/2022	15:00	20:00	07/09/2022	8	07/09/2022 00:00	07/09/2022 23:59
xyz	05/09/2022	08/09/2022	15:00	20:00	08/09/2022	9	08/09/2022 00:00	08/09/2022 20:00

Thank you

[jeffrock]

Hi, @Greg_Deckler,

please refer above 

need to create StartTime & End Time column

[Greg_Deckler]

@jeffrock So I am still not 100% sure what the final result is supposed to be. But to get the first or last of something like what you have, you can basically use the technique used in MTBF.

See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395....
The basic pattern is:
Column = 
  VAR __Current = [Value]
  VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])
  VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN

You'll have to adjust this obviously but for the start time, you want the MINX of the date based on certain conditions. Once you have that, compare to what is in the current row and if it is the same, return it, otherwise blank. For the end time, you want the MAXX.
  __Current - __Previous