generate time stamp between two time stamps?

[Anonymous]

Hello Everyone,

so I have these time stamps - 

Start Time	End Time
01/01/2022 10:00 AM	01/01/2022 10:30AM
01/01/2022 11:30 AM	01/01/2022 12:00PM

I want to generate the time stamp in between these two time stamps.

could you please guide me
It cannot be a measure. 

desired output : 

Start Time	End Time
01/01/2022 10:00 AM	01/01/2022 10:30AM
01/01/2022 10:30 AM	01/01/2022 11:30AM
01/01/2022 11:30 AM	01/01/2022 12:00PM

Thanks in advance 🙂 🙂 

[Anonymous]

I am trying to create a simulation in power BI.

[v-easonf-msft]

Hi, @Anonymous 

Try formuals as below:
Add a new calculated Table:

New Table = 
SAMPLE (
    3,
    UNION (
        SELECTCOLUMNS ( 'Table', "Start Time", 'Table'[Start Time] ),
        SELECTCOLUMNS ( 'Table', "Start Time", 'Table'[End Time] )
    ),
    [Start Time], ASC
)

Add a new calculated column:

End Time =
CALCULATE (
    MIN ( 'New Table'[Start Time] ),
    FILTER (
        'New Table',
        'New Table'[Start Time] > EARLIER ( 'New Table'[Start Time] )
    )
)

Please check my sample file for more details.

Best Regards,
Community Support Team _ Eason

[Anonymous]

You are close to the solution however, we are missing the first time duration ie. 1/1/2022 10:00 AM  - 1/1/2022 10:30 AM. and also the next time row 1/1/2022 10:30 AM - 1/1/20222 11:30 AM

[v-easonf-msft · Accepted solution]

Hi, @Anonymous 

My mistake, I just found out that I added the function "SAMPLE" to limit the result to 3 lines.(I added the sample function just to facilitate the sorting of Column ‘Start Time’)

Please modify my original formula as follows:

New Table = 
    UNION (
        SELECTCOLUMNS ( 'Table', "Start Time", 'Table'[Start Time] ),
        SELECTCOLUMNS ( 'Table', "Start Time", 'Table'[End Time] )
    )

Hope this could work for you.

Best Regards,
Community Support Team _ Eason

[Anonymous]

You are awesome 🙂 🙂 

[vojtechsima]

Hi, @Anonymous 
I am not sure what you're trying to accomplish,
You want duration between two datetimes or you have let's say 1/1/2022 10:00 AM and then you have 1/1/2022 12:30 and you want to generate timestamp in between, each after let's say 30 minutes?
So the outcome would be:
1/1/2022 10:30 AM
1/1/2022 11:00 AM
1/1/2022 11:30 AM

[Anonymous]

I have update the post.
Hopefully it helps.

Thank you!