topic Re: Timestamps and 'trading' dates in DAX Commands and Tips

Timestamps and 'trading' dates

Amylou12 — Fri, 13 Jun 2025 04:45:51 GMT

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!

Re: Timestamps and 'trading' dates

DataNinja777 — Fri, 13 Jun 2025 15:26:45 GMT

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,

Re: Timestamps and 'trading' dates

maruthisp — Sun, 15 Jun 2025 11:32:05 GMT

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

Re: Timestamps and 'trading' dates

Anonymous — Fri, 20 Jun 2025 08:44:50 GMT

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.

Re: Timestamps and 'trading' dates

Anonymous — Fri, 20 Jun 2025 09:17:05 GMT

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.

Re: Timestamps and 'trading' dates

Anonymous — Mon, 23 Jun 2025 08:50:18 GMT

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.

Re: Timestamps and 'trading' dates

AmylouWatts — Mon, 23 Jun 2025 09:16:55 GMT

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

Re: Timestamps and 'trading' dates

Anonymous — Tue, 24 Jun 2025 09:12:38 GMT

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!