Forum Discussion

pbicorp's avatar
pbicorp
Frequent Visitor
2 years ago

Summary Table With Lag Calculation

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 t...
Need Help
rajendraongole1's avatar
rajendraongole1
Super User
2 years ago

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's avatar
pbicorp
Frequent Visitor
2 years ago

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.