previous

[gastonguy]

Hello,

I have a table with different same object_number but the objects evolves over time and we can track it with the object_line_number. And every evolution of the object has a status.

I want to calculate the time elapsed between some status.

In SQL, the equivalent would be like this:

SELECT 
	SUM(CASE WHEN (DATEDIFF(MINUTE, T.date_received, T.date_planification) <= 15) THEN 1 ELSE 0 END)  /
	SUM(CASE WHEN (DAY(T.date_received) = DAY(T.date_planification)) THEN 1 ELSE 0 END) as result
FROM (
	SELECT *, T.status_id as current_status,
		LAG(T.fulldate_modification) OVER(PARTITION BY T.intervention_number ORDER BY T.intervention_line_number) as date_received,
		T.fulldate_modification as date_planification
	FROM 
	(
		select *,
			LAG (status_id) OVER(PARTITION BY intervention_number ORDER BY intervention_line_number) AS old_status
		from dbo.fact_intervention
	) AS T
	WHERE ((T.status_id = 0 AND (T.old_status <> 0 OR T.intervention_line_number = 1) AND T.emergency_level = 1) OR (T.old_status = 0 AND T.status_id IN (2,3,4,5)))--974
) AS T
WHERE old_status = 0

(Well maybe there is a better way to do it in SQL that I've done).

The column old_status is not

I am in DirectQuery mode, so a lot of functions are not present for Calculated Columns, that's why I am using Measures.

[austinsense]

Give this a try

= VAR objectnum = tablename[object_number],
      status = tablename[old_status],
      linenum = tablename[object_line_number]

RETURN

// We're going to iterate through the table, get an answer for each row, and then add them up at the end
SUMX(tablename,

// We'll start with the modification date for the current row

date_modification -

// then get and subtract the modification date for the previous record
CALCULATE(MAX(date_modification),
      FILTER(ALL(tablename),
           object_number = objectnum &&
           old_status = status &&
           object_line_number < linenum
      )
))