DAX / PowerPivot查询功能可在一段时间内传播聚合值-Java 学习之路

我正在尝试计算DAX表达式[对于Excel 2010中的MS PowerPivot]，以便在应用的范围内均匀分配值的总和，并在给定的时间 Span /周期内对其进行重新求和 . 在SQL服务器中交叉应用是微不足道的，尽管每次尝试都会导致相同的错误结果 .

我已将MS Project的输出保存为Excel并使用PowerQuery导入/转换，因此开始和结束/结束日期是正确的日期， h和 d是整数，日历之间的持续时间它们已经为模型计算/格式化 . 我还创建了一个日期表，其中包含从第一个日期到最后一个日期的连续日期，以及一个年份表，其中包含我想要汇总的4位数年份的字符串表示 .

该模型看起来像这样：

PowerPivot Model

我已经在ResourceQuery，TaskQuery和AssignmentQuery表上创建了计算列（所有这些都直接来自MS Project输出），以及ServiceAreaQuery（来自TaskQuery的唯一值...基本上是子项目） . 每个还有一个简单的度量，它是Assigned hours列的总和 .

数据本身看起来像您期望的Project 2010文件，并具有，和小时 . 任务的日期可以跨越1天到5年......这就是我的问题所在 .

如何为长时间运行的任务拆分/分块预先求和的值以匹配我正在寻找的时间间隔？

即使我使用日期表中的年份列，时间智能也没有 grab 它并且我已经没有计算CALCULATE（SUM（FILTER（COUNTROWS（DATESBETWEEN）））类型的东西了 .

有两个中间步骤，我试图找出无济于事 . 我认为它们都是通过相同的有效功能来解决，以达到小时的最终目标，服务区域，资源，年份 .

Pivot table to show

按资源划分的小时数，按年份计算
按服务区
小时，按年份计算

in order to show the end goal of

小时，按服务区，按资源，按年

您可以在下面的输出中看到该问题 .

Bad Output Example

请注意，当使用分配的小时总数和AssignmentQuery中的资源名称时，我得到了正确的总和，但是当使用任何日期值时......我只得到开始日期的小时数（模型中的活动关系） . 我需要的是这些时间在它们适用的时期内均匀分布（因此，如果在2016年1月1日至1月1日之间有1000小时，我希望每小时显示333小时） .

我最初的想法是选择器/过滤器/计算功能需要执行以下操作：

选择此人的小时数
从过滤器或列 Headers 中选择过滤到的时段中的天数（例如月/年/季/任何）
计算每天的小时数
获取过滤期间的工作日
从重叠中选择小时的总和

任何想法都非常感谢！我愿意做一些额外的ETL /数据创建作为PowerQuery步骤，但是我真的想为此找出正确的DAX表达式，因此它可以作为项目的时间切片器/过滤器 .

提前致谢 .

编辑发布修订版的答案

[Hours Apportioned Raw] :=
DIVIDE (
    CALCULATE (
                [Hours],
                FILTER (
                    AssignmentQuery,
                    AssignmentQuery[Start_Date] <= MAX ( Dates[Date] )
                        && AssignmentQuery[Finish_Date] >= MAX ( Dates[Date] )
                        )
                )
    , ( COUNTROWS (
                    DATESBETWEEN ( 
                                    Dates[Date]
                                    , FIRSTDATE ( AssignmentQuery[Start_Date] )
                                    , LASTDATE ( AssignmentQuery[Finish_Date] )
                                 )
                  )
      )
)

1 回答

5
鉴于你有一个相对复杂的模型并且你的要求并不是那么简单，我不确定这会让你一直到那里但是希望它至少会给你灵感，为你的目的修改它或开始更详细的讨论 .

以下措施有效地计算小时数，将它们应用于日期在开始和结束之间的日期，并将总数除以天数 . 稍微复杂的是需要迭代x2 - 一次超过日期，一次超过包含小时的表中的行 .

您可能遇到的问题是我使用的是未连接的日期表，如果您无法在模型中复制这种情况，那么我们需要尝试使用一些ALL（）函数 .

下面的解决方案假定一个名为“data”的表有4列：id，start，end，value和称为calendar的表，其中包含2列Date和Month .

措施1：总结小时数
```
[Hours] =SUM(Data[Value])
```
措施2：将时间应用于日期并除以日期数
```
[Hours Apportioned Raw] =
 CALCULATE ([Hours],
FILTER (
    Data,
    Data[Start] <= MAX ( Calendar[Date] )
        && Data[END] >= MAX ( Calendar[Date] )
       )
            )
/ ( MAX ( Data[End] ) - MAX ( Data[Start] ) )
```
测量3：在日期和ID上迭代测量2以给出正确的值
```
=
    SUMX (
        VALUES ( Calendar[Date] ),
        SUMX ( VALUES ( Data[ID] ), [Hours Apportioned RAW] )
    )
```
希望这有一定道理，非常简单的测试模型：Test Model

请注意，您需要下载模型，而不仅仅是在浏览器中查看它 .
回复于 2024-04-28T22:56:30+08:00

DAX / PowerPivot查询功能可在一段时间内传播聚合值

1 回答

相关问题