计算事件持续时间仅在切片时间内 [英] Calculate Event Durations to only part within Sliced Period

问题描述

我们正在研究PowerBI的报告解决方案，它可以满足我们的许多需求。但是，我们需要能够对事件进行即席报告。每个事件都有一个开始日期，一个结束日期，总秒数以及百分比计算等。这确实很好。

We are investigating PowerBI for a reporting solution and it does a lot of what we need. However we need to be able to do adhoc reporting on events. Each event has a start date and an end date and total seconds and do percentage calculations etc. This works really well.

但是，我们的共同要求是指定开始日期和我们希望考虑其数据的结束日期。许多事件将跨越所需时间段的开始和结束，甚至在时间段开始之前开始，并超出结束时间。我们需要做的就是仅考虑该时间段内的事件部分。

However our common requirement is to specify a start date and end date of which we wish to consider data. Many of the events will span over start and end of required period or even start before the start of period and go beyond the end. What we need to do is only consider the part of events that falls within the period.

是否可以使用切片器定义开始/结束时间段，然后仅包括为每个事件划分的时间段内的秒数？

Is it possible to use a slicer to define a start/end period and then only include the number of seconds within the period sliced for each event?

这将使我们能够计算该时间段内所有事件的总时间。

This would allow us to calculate the total time for all events that fell within the period.

我的表由成千上万的行组成，例如

My table consists of hundreds of thousands of rows like

EventID | VehicleID | StatusID | ReasonCodeID | StartDateTime | EndDateTime | TotalDuration

EventID|VehicleID|StatusID|ReasonCodeID|StartDateTime|EndDateTime|TotalDuration

我们需要查看每个事件在选定时间段内（开始/结束）的部分。但是，事件可能会持续一段时间。如果事件的一部分没有句点，我们将忽略持续时间的那一部分。如果整个事件不在选定的时间段内，那么我们将忽略所有事件。

We need to look at portions of each event that fall within a selected period (start/end). However events can span periods. If part of an event is outwith a period we want to ignore that part of the duration. If whole event is outside selected period then we would ignore all of it.

例如，某事件在2月1日开始，到7月1日结束。如果切片器的选定日期范围是1月1日至3月1日，那么我只想在总持续时间计算中包括2月1日至3月1日之间的时间

For example say an event starts on 1st Feb and goes to 1st July. If the selected date range of slicer was 1st Jan to 1st March then I want to only include the time between 1st Feb and 1st Mar in Total Duration calculation

推荐答案

您所要求的是可行的，但是它的工作原理在很大程度上取决于表中的数据量。

What you're asking for is doable, but how well it works would very much depend on the amount of data in your table.

您可以使用DAX度量来计算在给定日期发生的任何事件的持续时间之和。您可以使用切片时断开连接的日期表来执行此操作（2016年10月的Power BI Desktop版本包含内置的数据切片器，可让您轻松选择范围）。此处的更多信息： https ：//powerbi.microsoft.com/zh-CN/blog/power-bi-desktop-october-feature-summary/#reportView 。

You can use a DAX measure to calculate the sum of the duration for any events that fall on a given date. You can do this using a disconnected date table that you slice on (the October 2016 Power BI Desktop release includes a built-in data slicer that allows you to easily pick a range). More information on that here: https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-october-feature-summary/#reportView.

然后您将使用CALCULATE度量求和持续时间，并使用FILTER确保有问题的事件落在切片器选择的日期范围内。问题的此部分在此处有更多详细信息：优化Dax和放大器; 在…之间的日期模型

You would then have a CALCULATE measure that SUMS the duration, with FILTER to ensure the event in question lands within the date range selected by the slicer. There's more specifics on this part of the question here: Optimizing Dax & model for "where date between" type queries

但是，您要走的更远，因为您不希望累加给定范围内事件的完整持续时间日期范围-您只希望对给定日期范围内的持续时间求和。

However, you're going one step further, in that you don't want to SUM the full duration of events that fall within a given date range - you only want to sum the duration that falls within the given date range.

为此，您必须计算以下位置的每一行的持续时间：运行时间基于所选的日期范围。您可以使用使用SUMX的度量来执行此操作（请参见下文），但是在大量记录（成千上万）上，计算将开始变慢。

In order to do that, you have to calculate the duration for each individual row at run time based on the selected date range. You can do this with a measure that uses SUMX (see below), but over a large number of records (thousands, millions) the calculation will start slowing down.

例如，如果您有一个名为Date的断开连接的日期表，而您的事件表称为Event，则可以采用以下度量：

For example, if you have a disconnected date table called Date, and your event table is called Event, you can have a measure such as:

Filtered Duration =
CALCULATE (
    SUMX (
        Event,
        DATEDIFF (
            MAX ( MIN ( 'Date'[Date] ), Event[StartDateTime] ),
            MIN ( MAX ( 'Date'[Date] ), Event[EndDateTime] ),
            SECOND
        )
    ),
    FILTER (
        'Event',
        'Event'[StartDateTime] <= MAX ( 'Date'[Date] )
            && 'Event'[EndDateTime] >= MIN ( 'Date'[Date] )
    )
)

MIN（'Date '[Date]）在这种情况下，公式对应于未连接日期表中位于所选日期内的最早日期范围。 MAX（'Date'[Date]）对应最新日期。

MIN('Date'[Date]) in this case formula corresponds to the earliest date in the disconnected date table that is within the selected date range. MAX('Date'[Date]) corresponds to the latest date.

最后一部分（过滤器）的意思是仅查看在所选范围内的某个日期发生的事件。 SUMX说对于每一行，请执行DATEDIFF。 DATEDIFF中的MAX表示选择切片器中第一个日期或事件的开始日期中的较晚日期。这样做的原因是，如果您在日期切片器上选择15日到20日，而事件在18日开始，则您希望从18日开始计数。但是，如果活动在11日开始，则您要从15日开始算。 MIN与结束日期相反。

The last part (the FILTER) is saying "only look at events that land on a date within the range selected". The SUMX is saying "for each row, do a DATEDIFF". The MAX within the DATEDIFF is saying "choose the later of either the first date from the slicer, or the start date of the event". The reason for this is that if you select the 15th through the 20th on your date slicer, and an event starts on the 18th, you want to count from the 18th. But if the event started on the 11th, you'd want to count from the 15th. The MIN is doing the opposite with the end date.

如果某个事件完全落在该时间范围内，则它将计算从开始到结束的秒数。如果事件的结束时间在选定的时间范围之后（例如），则它将计算从事件的确切开始到选定的结束日期的午夜的秒数。

If an event falls within the time range entirely, then it will calculate the seconds from start to end. If the end of the event is after the selected time range (for example), then it will count the seconds from the exact start of the event to midnight of the selected end date.

请注意，由于我使用了日期表，因此无法选择部分日期作为日期范围。您可以将其扩展为包括一个时间表，但它会变得更加复杂（您需要一个单独的开始和结束时间表，然后在上面的公式（已经非常复杂的公式）中考虑该逻辑）

Note that because I've used a date table you wouldn't be able to pick partial days as a date range. You could extend this to include a time table but it gets more complicated (you'd need a separate start & end time table and then account for that logic in the above, already quite complex, formula)

这篇关于计算事件持续时间仅在切片时间内的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！