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

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.