Time interval

[Oded-Dror]

Hi there,
See image below.

In the left I have SQL Server table called example with two ID's  And DTTM columns.
I need it to capture 15 running min interval with reseting the loop (every 15 min set to 0 again)
In the middle I have a view I created which use TSQL with CTE first part grouping (anchor )
Second part the looping and reseting - 15 min time interval
Final part presenting the resultset

I've tried to translate TSQL to DAX but it produces wrong results
Any help will be much appriciated.

Thanks,
Oded Dror

[AmiraBedh]

To help you better, I would need to understand the logic you've implemented in T-SQL and see the DAX formula you've attempted. Unfortunately, the text in the image is not clear enough to read the details of the code. If you could type out the T-SQL query and the DAX formula you've tried, I could then provide more specific advice on how to correct the DAX formula.

Remember, in DAX, there is no direct equivalent of a CTE. Instead, you would use calculated columns, measures, or variables to achieve similar results. Moreover, the concept of "looping" in DAX doesn't work the same way as in T-SQL. You would typically use DAX time intelligence functions, filters, and iterators like CALCULATE, FILTER, and the X functions (like SUMX, AVERAGEX...) to perform row context and filter context operations over the data.

[Oded-Dror]

Hi Amira,

There is recursion in DAX see below 
https://stackoverflow.com/questions/52766022/recursion-in-dax
Thanks,
Oded Dror

[AmiraBedh]

But I need the dataset at least.

[Oded-Dror]

Amira,
Here is the TSQL code I used

drop table if exists example

CREATE TABLE example(
[ID] [varchar](9) NULL,
[DTTM] [datetime] NULL

) ON [PRIMARY]

Insert into [example] (ID,DTTM) values
('123456789','2017-10-05 08:00:00.000'), -- Expected Result
('123456789','2017-10-05 08:05:00.000'),
('123456789','2017-10-05 08:07:00.000'),
('123456789','2017-10-05 08:15:00.000'), -- Expected Result
('123456789','2017-10-05 08:25:00.000'),
('123456789','2017-10-05 10:12:00.000') , -- Expected Result
('123456789','2017-10-05 10:26:00.000'),
('123456789','2017-10-05 10:32:00.000'), -- Expected Result
('123456789','2017-10-05 10:33:00.000'),
('123456789','2017-10-05 10:34:00.000'),
('123456789','2017-10-05 10:35:00.000'),
('123456789','2017-10-05 10:36:00.000'),
('123456789','2017-10-05 10:37:00.000'),
('123456789','2017-10-05 10:38:00.000'),
('123456789','2017-10-05 10:39:00.000'),
('123456789','2017-10-05 10:40:00.000'),
('123456789','2017-10-05 10:41:00.000'),
('123456789','2017-10-05 10:42:00.000'),
('123456789','2017-10-05 10:43:00.000'),
('123456789','2017-10-05 10:44:00.000'),
('123456789','2017-10-05 10:45:00.000'),
('123456789','2017-10-05 10:46:00.000'),
('123456789','2017-10-05 10:47:00.000'), -- Expected Result
('123456789','2017-10-05 10:48:00.000')

Insert into [example] (ID,DTTM) values
('789','2017-10-05 08:00:00.000'), -- Expected Result
('789','2017-10-05 08:05:00.000'),
('789','2017-10-05 08:07:00.000'),
('789','2017-10-05 08:15:00.000'), -- Expected Result
('789','2017-10-05 08:25:00.000'),
('789','2017-10-05 10:12:00.000') , -- Expected Result
('789','2017-10-05 10:26:00.000'),
('789','2017-10-05 10:32:00.000'), -- Expected Result
('789','2017-10-05 10:33:00.000'),
('789','2017-10-05 10:34:00.000'),
('789','2017-10-05 10:35:00.000'),
('789','2017-10-05 10:36:00.000'),
('789','2017-10-05 10:37:00.000'),
('789','2017-10-05 10:38:00.000'),
('789','2017-10-05 10:39:00.000'),
('789','2017-10-05 10:40:00.000'),
('789','2017-10-05 10:41:00.000'),
('789','2017-10-05 10:42:00.000'),
('789','2017-10-05 10:43:00.000'),
('789','2017-10-05 10:44:00.000'),
('789','2017-10-05 10:45:00.000'),
('789','2017-10-05 10:46:00.000'),
('789','2017-10-05 10:47:00.000'), -- Expected Result
('789','2017-10-05 10:48:00.000')

Thanks,

Oded Dror

[Ahmedx]

Please check this '789','2017-10-05 10:32:00.000')
('789','2017-10-05 10:47:00.000'
does not fall within the 15 minute interval

[Oded-Dror]

The running total is reseting every 15 min. where is 10:32 (10:32 - 10:12 = 20)

[gmsamborn]

Hi @Oded-Dror 

I added a time dimension with 1 row per minute.  It also has a 15-minute bucket.  (I added a DimDate table.)

After adding a couple of relationships and measures, my model is below.

DimTime - 15 minute buckets.pbix

Let me know if you have any questions.

[Oded-Dror]

Amira,
Thank you for solving this issue, it works! 

Point to remember DAX dateadd func dosen't have Minute interval like SQL dose.
Thanks again

Oded Dror

[Oded-Dror]

Amira,

This is not accurate if you see in your result 10:26 - 10:12 is 14 min and not 15
And the last record as well

Thanks,

Oded Dror

[gmsamborn]

Hi @Oded-Dror 

I see now that the 10:26 record is wrong.  I'll let you know when I update it.

[gmsamborn]

Hi @Oded-Dror 

10:26 DOES belong in the Group 10:15 since that group would span from 10:15 to 10:30.

Please double-check your findings.

DimTime - 15 minute buckets.pbix

[Oded-Dror]

It's still not accurate it sould be 5 records per group 8:00 , 8:15, 10:12, 10:32 , 10:47

[gmsamborn]

Hi @Oded-Dror 

OK.  Lets look at the 2nd page.  When you click on a line in the upper table, it will filter the bottom table.

For 8:00, the earliest is at 8:00.  There is also one at 8:05 and another at 8:07.

For 8:15, the earliest is at 8:15.  There is also one at 8:25.

etc.

DimTime - 15 minute buckets.pbix

[Oded-Dror]

Please see the TSQL code abouve (I sent to Amira this morning)

[gmsamborn]

I've looked at it and as Amira pointed out, you can't use CTEs.

Please explain what is wrong with my version.  It seems to work for me and you still haven't shown an example where it doesn't work.

Please refer to the 2nd page of my pbix.

[Oded-Dror]

See The Excel sheet - it take 4 iterations (Running Total every 15 min) your solution show 6 record per group also 10:26 - 10:12 = 14

[gmsamborn]

@Oded-Dror 

If you are using 15 minute buckets, why isn’t 10:26 highlighted?
10:12 would be in the '10:00 to 10:15' bucket whereas 10:26 would be in the '10:15 to 10:30' bucket.

OR do you mean that the bucket start resets based on something?  If you mean a floating 15 minute bucket, the Time dimension's buckets won't help at all.

I need more details

[Oded-Dror]

Look at the Excel sheet the interval is reset everytime the running total reach 15 min
Remember 10:12 was the cutting point now we are in 0 again

therefore 10:26 - 10:12 = 14 (we need 1 more to get 15) than 10:32 - 10:26 = 6 

6 + 14 = 20 is >= 15 yes so 10:32 is the correct result
In another word is the running total of delta need to reach 15 min and reset again to 0 and start over thats way your windows of 15 min bucket won't produce the expected result it has to be recursive like TSQL

[gmsamborn]

Sorry.  I thought you meant 15 minutes as in a Time dimension.

I'll look at doing it without the time dimension but DAX doesn't support recursion.  any use of a CTE is out.

[Oded-Dror]

Yes, the Windows function in DAX may help?