在PowerBI中创建时间因素 [英] Creating time factors in PowerBI

问题描述

我有小时的数据。

我想在不同的时间因素中格式化和查看这些值的平均值。如每日，每周，每月，每夜（晚上9点至6点）等。

我已经查看了新的列的拼接和DAX格式，并且一直在玩弄 - 但是到目前为止还没有完成。

数据示例

 日期值
 28-06 -2016 01:00 1 
 28-06-2016 02:00 1 
 28-06-2016 03:00 6 
 28-06-2016 04:00 9 
 28 -06-2016 05:00 15 
 28-06-2016 06:00 17 
 28-06-2016 07:00 18 
 28-06-2016 08:00 21 
 28-06-2016 09:00 25 
 28-06-2016 10:00 30 
 28-06-2016 11:00 38 
 28-06-2016 12:00 41 
 28-06-2016 13:00 43 
 28-06-2016 14:00 45 
 28-06-2016 15:00 50 
 28-06-2016 16:00 57 
 28-06-2016 17:00 58 
 28-06-2016 18:00 60 
 28-06-2016 19:00 70 
 28-06-2016 20 ：00 77 
 28-06-2016 21:00 85 
 28-06-2016 22:00 85 
 28-06-2016 23:00 91 
 29-06- 2016 00:00 91 
 29-06-2016 01:00 97 
 29-06-2016 02:00 99 
 29-06-2016 03:00 99 
 29- 06-2016 04:00 100 
 29-06-2016 05:00 104 
 29-06-2016 06:00 113 
 29-06-2016 07:00 122 
 29-06-2016 08:00 125 
 29-06-2016 09 ：00 126 
 29-06-2016 10:00 134 
 29-06-2016 11:00 143 
 29-06-2016 12:00 150 
 29-06- 2016 13:00 158 
 29-06-2016 14:00 162 
 29-06-2016 15:00 171 
 29-06-2016 16:00 174 
 29- 06-2016 17:00 179 
 29-06-2016 18:00 181 
 29-06-2016 19:00 185 
 29-06-2016 20:00 195 
 29-06-2016 21:00 197 
 29-06-2016 22:00 203 
 29-06-2016 23:00 205 
  

解决方案

我将在Power BI Desktop的编辑查询窗口中解决这个问题。此组件以前称为功能查询，当它只是一个Excel加载项。

在编辑查询/ PQ中，有一个更丰富的Date功能集应该符合您的要求：

https://msdn.microsoft.com/en-us/library/mt296606.aspx

我也找到了并且测试任务更容易。您甚至可以将您的查询复制到&从Excel PQ获得很好的考试/评价经验。

I have data on a hourly basis.

I would like to format and view the mean of these values across different time factors. Such as daily, weekly, monthly, nightly (9 pm to 6 am) and so on.

I've looked at splices and the DAX formatting of new columns and been playing around with that - but so far been unsuccesful.

Data example

Date                Value
28-06-2016 01:00    1
28-06-2016 02:00    1
28-06-2016 03:00    6
28-06-2016 04:00    9
28-06-2016 05:00    15
28-06-2016 06:00    17
28-06-2016 07:00    18
28-06-2016 08:00    21
28-06-2016 09:00    25
28-06-2016 10:00    30
28-06-2016 11:00    38
28-06-2016 12:00    41
28-06-2016 13:00    43
28-06-2016 14:00    45
28-06-2016 15:00    50
28-06-2016 16:00    57
28-06-2016 17:00    58
28-06-2016 18:00    60
28-06-2016 19:00    70
28-06-2016 20:00    77
28-06-2016 21:00    85
28-06-2016 22:00    85
28-06-2016 23:00    91
29-06-2016 00:00    91
29-06-2016 01:00    97
29-06-2016 02:00    99
29-06-2016 03:00    99
29-06-2016 04:00    100
29-06-2016 05:00    104
29-06-2016 06:00    113
29-06-2016 07:00    122
29-06-2016 08:00    125
29-06-2016 09:00    126
29-06-2016 10:00    134
29-06-2016 11:00    143
29-06-2016 12:00    150
29-06-2016 13:00    158
29-06-2016 14:00    162
29-06-2016 15:00    171
29-06-2016 16:00    174
29-06-2016 17:00    179
29-06-2016 18:00    181
29-06-2016 19:00    185
29-06-2016 20:00    195
29-06-2016 21:00    197
29-06-2016 22:00    203
29-06-2016 23:00    205

解决方案

I would tackle this back in the Edit Queries window of Power BI Desktop. This component was formerly known as Power Query back when it was just an Excel Add-In.

In Edit Queries / PQ, there is a richer set of Date functions that should meet your requirements:

https://msdn.microsoft.com/en-us/library/mt296606.aspx

I also find the build and test tasks much easier there. You can even copy your Queries to & from Excel PQ for a great test/review experience.

这篇关于在PowerBI中创建时间因素的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！