Summary Table With Lag Calculation

[pbicorp]

I am working with a summary table and must create a lag from the Value (minutes) column. The calculated column cannot exceed 15 (as noticed from the Datetime column being 15 min increments). I have tried almost everything with creating indexes for the entire dataset and for each Person/ DateTime grouping with little to no luck calculating the lag. In this dataset, there is only one Person, but there are more so both Person and Datetime are essential for the lag calculation. Some values can overlap, as seen on 1/1/24 at 1:45, and must be added to the Lag output.

I'm excited to see how I should go about this as I have been able to get the value to lag one row after rather than filling the rest or getting a beautiful "not enough memory" message during the calculation.

DateTime	Person	Value	Desired Lag Output
1/1/24 1:00	Joey	55	15
1/1/24 1:15	Joey		15
1/1/24 1:30	Joey		15
1/1/24 1:45	Joey	2	12
1/1/24 2:00	Joey	25	15
1/1/24 2:15	Joey		10
1/1/24 2:30	Joey	16	15
1/1/24 2:45	Joey	4	5

[rajendraongole1]

Hi @pbicorp - you can create a calculated column as below to get the desired output.

LagOutput =
VAR CurrentDateTime = 'Table'[DateTime]
VAR CurrentValue = 'Table'[Value]
VAR PreviousRows =
FILTER(
'Table',
'Table'[Person] = EARLIER('Table'[Person]) &&
'Table'[DateTime] < CurrentDateTime
)
VAR CumulativeLag =
SUMX(PreviousRows,
MIN(15, IF('Table'[Value] = BLANK(), 15, 'Table'[Value]))
)
VAR LagWithCurrent = CumulativeLag + MIN(15, IF(CurrentValue = BLANK(), 15, CurrentValue))
RETURN
IF(CurrentValue = BLANK(), MIN(15, LagWithCurrent), MIN(15, CumulativeLag))

tested in power bi below is the output you can see in table chart.

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[pbicorp]

Sanity check. So, I entered the dataset as I presented it in my initial ask as a manually entered table. I then proceeded to make the calculated column with the logic you presented, but I am still not getting your results. 

I am completely confused on how this is.

[pbicorp]

Unfortunately, this is still not working for me. Could this be because I am working with a summarized table rather than a direct data source? Is there anything else I should look into or that I could provide to help solve this? I would like to state that there are/could be more blank values before and after datetimes that have a value. I tried to condense the dataset but that may have impacted how you carried out your calculation.