Forum Discussion
Average Lag in DAX from SQL Server
I am trying to compute the average lag for each location using DAX.
My table has 3 columns: AddressId, DeliveredDateCT, and Deliveries.
The Deliveries column is the total number of deliveries received on that day with days that have no delivers being 0 not NULL.
I use this code to find the average lag in SQL Server:
select AddressId,
avg(datediff(day, PriorDate, DeliveredDateCT)) AverageLag
from (
select AddressId,
DeliveredDateCT,
PriorDate = LAG(DeliveredDateCT, 1)
over (partition by AddressId order by DeliveredDateCT)
from #DeliveryReport
where Deliveries > 0
) lag
group by AddressId
Is this something that can be translated from SQL to DAX?
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! 🙂
4 Replies
Can you provide some sample data and expected output? I don't fully follow your SQL query to be honest.
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! 🙂
The inner query is pulling all the delivery dates and finding their previous delivery date then the outer query is averaging all the date diffs for each location to find out on average how frequently they get deliveries.
The expected result would be the average for each location.
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! 🙂
