Find Previous 5 Minutes Value

[DataGirl90]

Hi, 

I'm looking to find the Sum of Value for the Previous 5 Minutes. I'm connected via Direct Query and am not able to use CALCULATE in a column. My Date Time field is in 5 minutes increment. The sample below is what I'm trying to achieve. There will be a date slicer on the page so users will be able to select the dates they would like the data for. 

DateTime	Value	Value Prev 5 Minutes	% Change
2/2/2023 9:15:00 AM	50	25	100%
2/2/2023 9:20:00 AM	100	50	50%
2/2/2023 9:25:00 AM	150	100	33%
2/2/2023 9:30:00 AM	200	150	25%
2/2/2023 9:35:00 AM	250	200	20%
2/2/2023 9:40:00 AM	300	250	16%

I've tried

CALCULATE(
SUM(Value)),
ALL(Date Time Field),
Date Time Field = MAX(Date Time Field) - TIME (0,5,0)
*This DAX worked on some rows but not all. On different days, missing values were occurring at the same time interval

CALCULATE(
SUM(Value)),
ALL(Table),
Date Time Field <= EARLIER(Date Time Field)
*This DAX doesn't work because I cannot create a column using CALCULATE with Direct Query

I've also created a column with the -5 minutes and called it Prev 5 Mins and tried this DAX below
SUM(Value)),
ALL(Table),
Date Time Field = Prev 5 Mins
*This one was returning blanks.

Can someone help me, please?

I'd like to add that I have been able to successfully use the below dax
Measure 1 = 
VAR CurrentDate = SELECTEDVALUE(Table [Date])
VAR PreviousDate = 
CALCULATE(
MAX(Table[Date]),
TAble[Date] < CurrentDate)
RETURN

CALCULATE([Measure Sum]), Table[Date] = PreviousDate

This DAX does work, however, given the size of my dataset, I do sometimes get the 1 mil row error problem and it does take some time for the results to display. I'm hoping someone would be able to advise me on a better method.

[lbendlin]

use two relative time filters on your visual. Something like this:

- is in last ten minutes

- is not in last five minutes

[DataGirl90]

Hi, 

Applying a visual level filter would filter out the other fields in my matrix though. Also, I should have provided more information. There is a date slicer that users can use to select the dates they would like the data for. 

[lbendlin]

Create a measure that implements your logic and use that measure as a visual filter.

[DataGirl90]

Hi, 

That's what I was asking for help with. I've updated the question with some more DAX and I was wondering if there is a better way to write the DAX. Also, I'm not sure what you mean by creating a measure and using that measure as a visual filter. I don't think that using a measure to apply a visual filter would satisfy the requirements of the report. As I've explained above, if I apply a filter on the visual, the dates would have to match the dates the users will select in the date slicer seeing as it is expected report behavior that all the visuals on the report page will interact with each other. And what measure were you thinking of specifically that I should use as a filter? My main problem in the initial post was calculating the data for the previous 5 minutes from the selected date time and then calculating the % change from it.

[lbendlin]

There is a date slicer that users can use to select the dates they would like the data for

is that fed from your fact table or from a connected dimension table (which would be weird because of the 5 minute granularity)?

I tested with measures and it works fine even considering the selection slicer being fed from the fact table.