Как вычесть даты из разных строк в POWER BI

У меня есть таблица, которая выглядит так:

введите здесь описание изображения

Потерпите меня, это немного запутает;

Я хочу получить значения в нужном столбце. Вычисление: Время начала-(предыдущая строка)Время окончания. Таким образом, строка 2 (Время начала) минус строка 1 Время окончания будет 17:04:48 минус 17:04:31 (= 17 секунд). Однако я хочу исключить строки, в которых CustAgentFl = 0 AND Transferflag = 0 перед выполнением вычисления вычитания даты. Кроме того, если Starttime-Endtime равно ‹0, то просто 0.

Все строки сгруппированы по одному и тому же NID, поэтому, конечно, запрос DAX должен быть сгруппирован по NID.


sql-server powerbi datetime dax date-difference
person dragonfury2    schedule 04.11.2018    source источник
comment
Вы можете делать запросы на SQL-сервере? Какую версию ты используешь? если 2012 и выше, вы можете использовать функцию Lead, чтобы получить желаемый результат.   -  person Harry    schedule 05.11.2018
comment
В DAX (или вообще в любой базе данных) нет такой вещи, как предыдущая строка. Вы должны определить, что это означает в вашем случае. Например, вы можете присвоить своим записям порядковые номера в зависимости от их времени, а затем использовать эти номера (индекс) для поиска предыдущей записи.   -  person RADO    schedule 05.11.2018
comment
Да, я использую 2012. Как мне использовать функцию «Лид»? Я считаю, что это функция окна, которая устраняет необходимость в последовательной нумерации строк, но не очень удобна в ее использовании.   -  person dragonfury2    schedule 05.11.2018

Ответы (1)


arrow_upward
3
arrow_downward

Если вы можете использовать базу данных для расчета этих значений, вы можете использовать LAG оконная функция для получения значения из предыдущей строки. Однако в базах данных нет очевидной предыдущей строки, поскольку порядок строк не определен. Итак, чтобы получить значение предыдущей строки, вы должны определить порядок. Это цель ORDER BY в OVER. Я предполагаю, что ваш заказ к StartTime. В этом случае вы можете использовать такой код:

declare @Table table(NID int, DgAcs char(1), CustAgentFl int, AgentId int, StartTime datetime, EndTime datetime, TransferFlag int, Desired int)

insert into @Table values
    (4565,  'C', 1, 358746, '2018-09-08 17:02:37', '2018-09-08 17:04:31', 1, 0), 
    (4565,  'C', 1, 358714, '2018-09-08 17:04:48', '2018-09-08 17:08:17', 1, 17), 
    (4565,  'C', 1, 359548, '2018-09-08 17:07:07', '2018-09-08 17:13:41', 1, 0), 
    (4565,  'C', 1, 358749, '2018-09-08 17:13:54', '2018-09-08 17:21:09', 1, 13), 
    (4565,  'A', 1, 351897, '2018-09-08 17:19:09', '2018-09-08 17:20:36', 0, 0), 
    (4565,  'C', 1, 358896, '2018-09-08 17:21:08', '2018-09-08 17:26:00', 0, 0)


; with cte as (
select
    *
    , LAG(EndTime, 1) over(order by StartTime) as PrevEndTime
    , DATEDIFF(s, LAG(EndTime, 1) over(order by StartTime), StartTime) as SecondsSinceLastEndNullable
    , ISNULL(DATEDIFF(s, LAG(EndTime, 1) over(order by StartTime), StartTime), 0) as SecondsSinceLastEnd
from @Table 
)

select
    *
    , iif(SecondsSinceLastEnd <= 0, '00:00:00', concat(SecondsSinceLastEnd / 3600, ':', FORMAT((SecondsSinceLastEnd / 60) % 60, 'D2'), ':', FORMAT(SecondsSinceLastEnd % 60, 'D2')))
    , iif(SecondsSinceLastEnd <= 0, '00:00:00', CONVERT(varchar, DATEADD(s, SecondsSinceLastEnd, 0), 108))
from cte

Вы отформатировали желаемый результат как время, но обратите внимание, что разница между двумя моментами времени — это не совсем время. Это может быть более 24 часов. Я показал вам два способа конвертировать разницу в количестве секунд в нужный формат. Второй использует CONVERT для получения значения TIME, но, как я уже сказал, это не будет работать для промежутков более 24 часов. Первый способ даст вам продолжительность в формате H:MM:SS, где H — количество часов, которое может быть больше 23.

В Power BI это можно записать как пользовательский запрос. .

person Andrey Nikolov    schedule 05.11.2018
comment
Это именно то, что мне нужно. Браво сэр - person dragonfury2; 05.11.2018