Timestamps and 'trading' dates

[Amylou12]

I am wanting to group timestamped data into 15 minute intervals.

And also to reflect a 'trading' date that extends past midnight (ie trading date for 13 June is from "13062025 06:00 to 14062025 05:59"/ trading date for 14 June is "14062025 06:00 to 15062025 05:59" etc)

Thank you!

[v-tejrama]

Hi @Amylou12 ,

Just wanted to check if you had the opportunity to review the suggestion provided by @maruthisp  and @DataNinja777 .

If the response has addressed your query, please "Accept it as a solution"and give a 'Kudos' so other members can easily find it.

Thank you.

Tejaswi.

[v-tejrama]

Hi @Amylou12,

I hope the information provided has been useful. Please let me know if you need further clarification or would like to continue the discussion.

If your question has been answered, please “Accept as Solution” and Give “Kudos” so others with similar issues can easily find the resolution.

Thank you.

Tejaswi.

[v-tejrama]

Hi @Amylou12,

I wanted to follow up and see if you had a chance to review the information shared. If you have any further questions or need additional assistance, feel free to reach out.

If the response answered your query, kindly “Accept as Solution” and Give “Kudos” to help others in the community benefit from it as well.

[AmylouWatts]

Thank you, I haven't had a chance to e to revisit this as yet...when I do I will let you know how I go. Thx again 

[v-tejrama]

Hi @AmylouWatts,

No worries at all take your time. Once you’ve had a chance to review or try it out, just let me know how it goes.

I’m here if you need any further help.

Thanks again!

[maruthisp]

Hi Amylou12,

As per my understanding, I try to implement a solution for your problem as per the original post:
Timestamps and trading dates.pbix

Please let me know if there is any missing things in the pbix file.

If this reply helped solve your problem, please consider clicking "Accept as Solution" so others can benefit too. And if you found it useful, a quick "Kudos" is always appreciated, thanks! 

Best Regards, 

Maruthi 

LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/ 

X            -  Maruthi Siva Prasad - (@MaruthiSP) / X

[DataNinja777]

Hi @Amylou12 ,

To group your timestamped data into 15-minute intervals and also assign a custom trading date that starts at 6:00 AM and runs until 5:59 AM the next day, you can handle this in either Power Query or DAX.

In Power Query, you can create the 15-minute bucket by rounding down the minutes of your timestamp and creating a new datetime value that reflects the rounded time:

= Table.AddColumn(Source, "15 Min Interval", each 
    #datetime(
        Date.Year([Timestamp]), 
        Date.Month([Timestamp]), 
        Date.Day([Timestamp]), 
        Time.Hour([Timestamp]), 
        Number.RoundDown(Time.Minute([Timestamp]) / 15) * 15, 
        0
    ))

Then, to determine the trading date, which starts at 6:00 AM, you compare the time portion of your timestamp. If the time is before 6:00 AM, subtract one day from the date. Otherwise, use the current date:

= Table.AddColumn(Source, "Trading Date", each 
    let ts = [Timestamp] in 
    if Time.From(ts) < #time(6,0,0) then Date.AddDays(Date.From(ts), -1) else Date.From(ts))

If you're working in DAX and prefer calculated columns, the logic is similar. For the trading date:

Trading Date = 
VAR ts = 'YourTable'[Timestamp]
RETURN IF(
    TIME(HOUR(ts), MINUTE(ts), SECOND(ts)) < TIME(6,0,0),
    DATE(YEAR(ts), MONTH(ts), DAY(ts)) - 1,
    DATE(YEAR(ts), MONTH(ts), DAY(ts))
)

And for the 15-minute interval rounding in DAX:

15 Min Interval = 
VAR ts = 'YourTable'[Timestamp]
RETURN
    DATETIME(
        YEAR(ts), 
        MONTH(ts), 
        DAY(ts), 
        HOUR(ts), 
        INT(MINUTE(ts) / 15) * 15, 
        0
    )

This will give you a clean and aligned way to analyze data based on trading hours that don’t follow the standard calendar day.

Best regards,