Average Lag in DAX from SQL Server

Alright thanks I see what you are getting at. I created a calculated table with the following DAX (your table is referenced as Deliveries, your column names are the same:

LagTable = 
VAR tmpTable = FILTER(Deliveries, Deliveries[Deliveries] > 0)
VAR tablePrevDate = ADDCOLUMNS(tmpTable, "PrevDate", MAXX(FILTER(tmpTable, [AddressId] = EARLIER([AddressId]) && [DeliveredDateCT] < EARLIER([DeliveredDateCT])), [DeliveredDateCT]))
VAR tableLags = ADDCOLUMNS(FILTER(tablePrevDate, NOT(ISBLANK([PrevDate]))), "Lag", DATEDIFF([PrevDate], [DeliveredDateCT],DAY))
RETURN 
SUMMARIZE(tableLags, [AddressId], "AvgLag", AVERAGEX(FILTER(tableLags, [AddressId] = EARLIER([AddressId])), [Lag]))

This can be a lot simpler but I wanted to clearly show the logic.
VAR tmpTable: First we filter your table to filter out the 0 deliveries (they are noise).

VAR tablePrevDate: We add a column to tmpTable, by taking the max date in a filtered tmpTable (filtered on current AddressID and Date < current row context date)

VAR TableLags: Adds a column to a filtered tablePrevDate (which is filtered to remove everything without a prevDate value) and add a Lag calculation
RETURN: a summary table per AddressID, with a column AvgLag.

Let me know if this works for you! 🙂

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂