在Power BI中基于时间范围用Power Query/DAX动态计算调整时长

核心思路 #

不再仅根据状态开始时间筛选记录，而是计算每个状态时间区间与目标统计窗口的交集时长，确保跨边界的状态时长被准确统计。

具体步骤（以Python Pandas为例） #

假设你的数据集已加载为DataFrame df，包含State、calculated duration（秒）、time（状态开始时间）三列：

1. 标准化时间格式
   先将time列转为datetime类型，方便后续计算：

   import pandas as pd
   df['time'] = pd.to_datetime(df['time'])
   

2. 定义统计窗口
   明确目标时间范围：比如针对日期2024-05-20，窗口为2024-05-20 06:00:00到2024-05-21 06:00:00：

   window_start = pd.to_datetime('2024-05-20 06:00:00')
   window_end = window_start + pd.Timedelta(days=1)
   

3. 计算状态结束时间
   根据开始时间和持续时长，算出每个状态的结束时间：

   df['end_time'] = df['time'] + pd.Timedelta(seconds=df['calculated duration'])
   

4. 计算窗口内有效时长
   写一个函数，计算每个状态时间区间与统计窗口的交集时长（秒）：

   def get_overlap_seconds(row):
       state_start = row['time']
       state_end = row['end_time']
       # 取两个区间的交集起始和结束时间
       overlap_start = max(state_start, window_start)
       overlap_end = min(state_end, window_end)
       # 若交集存在则返回时长，否则返回0
       return (overlap_end - overlap_start).total_seconds() if overlap_start < overlap_end else 0
   
   df['valid_duration'] = df.apply(get_overlap_seconds, axis=1)
   

5. 按状态汇总总耗时
   最后分组求和，得到各状态在目标窗口内的总耗时：

   total_time_by_state = df.groupby('State')['valid_duration'].sum()
   # 可选：将秒数转为时分秒格式，更直观
   total_time_by_state = total_time_by_state.apply(lambda x: str(pd.Timedelta(seconds=x)))
   

针对你的示例场景 #

比如某processing状态开始于2024-05-20 02:25:00，持续3小时35分钟（12900秒），结束时间为2024-05-20 06:00:00。若统计窗口是2024-05-19 06:00:00到2024-05-20 06:00:00，则该状态的整个时长都在窗口内，valid_duration会被计算为12900秒，最终被纳入processing状态的总耗时。

内容的提问来源于stack exchange，提问作者etraud7