DAX / PowerPivot查询功能用于在时间段内扩展聚合值 [英] DAX / PowerPivot query functions to spread aggregated values over time period

问题描述

我正在努力制定DAX表达式（对于Excel 2010中的MS PowerPivot），将值应用到的范围之间均匀分配一个值，并在给定的时间跨度/期间重新计算。在SQL服务器中交叉应用是微不足道的，尽管每次尝试都会产生相同的错误结果。

我将MS Project的输出保存为Excel并使用PowerQuery导入/转换，所以开始和结束日期是适当的日期，{X} h和{Y} d是整数，并且它们之间的日历日期持续时间已经被计算/格式化为模型。我还有一个创建的日期表，它具有从第一个日期到最后的连续日期，以及一个具有我要总结的4位数年份的字符串表示形式的年表。

模型如下所示：

我已经在ResourceQuery，TaskQuery和AssignmentQuery表中创建了计算列（全部直接从MS Project输出）和ServiceAreaQuery（来自TaskQuery ...本质上是子项目的唯一值）。每个还有一个简单的度量，即分配的时间列的总和。

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

如何将长时间运行的任务的预估值分割/块以匹配我要查找的时间间隔？

即使我使用日期表中的年份列，时间智能也没有抓住它CALCULATE（SUM（FILTER（COUNTROWS（DATESBETWEEN））））类型的东西，我的用法不足。

有两个中间步骤我试图找出无效。我想象，他们都是通过相同的有效功能来解决的，按照年份按照资源，按照服务区域获得小时的终端目标。

要显示的数据透视表

• 按资源分列的年份


• 按服务区域的小时，按年份

为了显示最终目标 / p>

• 按服务区域按照资源，按年份

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

请注意，当使用总分配的小时数和资源名称来自AssignmentQuery时，我得到正确的总和，但是当使用任何日期值...我只得到开始日期的时间（模型中的活跃关系）。我所需要的是在这些时间内平均分配到适用的时间（所以如果某事在1/1/16到1/1/19之间有1,000小时，我希望能够显示333小时/年）

我最初的想法是，selector / filter / calculate函数需要执行以下操作：

• 选择人员的时间


• 从过滤器或列标题中选择过滤到（例如月/年/季/无论）的期间内的天数


• 计算每天的小时数


• 在过滤的时间段内获取工作时间


• 从重叠的时间

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

提前感谢

**编辑发布修改版本的答案提供**

  [小时分配原始]：= 
 DIVIDE（
 CALCULATE（
 [Hours]，
 FILTER（
 AssignmentQuery，
 AssignmentQuery [Start_Date]< = MAX（Dates [Date]）
&& AssignmentQuery [Finish_Date]> = MAX（Dates [Date]）
 
）
，（COUNTROWS（
 DATESBETWEEN（
日期[日期] 
，FIRSTDATE（AssignmentQuery [Start_Date]）
 ，LASTDATE（AssignmentQuery [Finish_Date]）
）
）
）
）
  

解决方案

鉴于您有一个相对复杂的模型，您的要求并不完全直截了当，我不知道这会让你一路走来，希望至少可以为您提供灵感，为您的目的进行修改或开始更详细的讨论。

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

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

下面的解决方案假定一个名为data的表具有4列：id，start，end，value和表，日期和月份。

度量1：总和小时

 小时] = SUM（数据[值]）
  

措施2：按日期数量

  [小时分配原始] = 
 CALCULATE（[Hours]，
 FILTER 
数据，
数据[开始]< = MAX（日历[日期]）
&& Data [END]> = MAX（Calendar [Date]）
 ）
）
 /（MAX（Data [End]） -  MAX（Data [Start]））
  

度量3：迭代度量2超过日期和ids以提供正确的值

  = 
 SUMX（
 VALUES（Calendar [Date]），
 SUMX（VALUES（Data [ID]），[小时分配RAW ]）
）
  

希望这有一些意义，非常简单的测试模型在这里：测试模型

请注意，您不需要下载模型在浏览器中查看。

I’m trying to work out the DAX expression [for MS PowerPivot in Excel 2010] to evenly distribute the sum of a value across the range it’s applied to, and re-sum that up for a given time span/period. It’s trivial to cross-apply in SQL server, though with every attempt, end up with the same wrong result.

I’ve got the output from MS Project saved as Excel and imported/transformed using PowerQuery, so the start and finish/end dates are proper dates, the {X}h and {Y}d are integers, and the calendar day duration between them are already calculated/formatted for the model. I also have a dates table created that has the contiguous dates from the first date through the last, and a years table that has the string representation of the 4 digit years I want to summarize by.

The model looks like so:

I have created calculated columns on the ResourceQuery, TaskQuery and AssignmentQuery tables (all directly taken from the MS Project output), and on the ServiceAreaQuery (unique values from TaskQuery … essentially subprojects). Each also has a simple measure that is the sum of the Assigned hours column.

The data itself looks like you’d expect from a Project 2010 file, and has a {start_date}, {finish_date} and hours. The dates for a task can span anywhere from 1 day to 5 years … and this is where my problem lies.

How do I split/chunk the pre-summed value for a long running tasks to match the time interval I’m looking for?

Even if I use the year column from the date table, the time intelligence doesn’t catch it & I’m running out of ideas for a CALCULATE(SUM(FILTER(COUNTROWS(DATESBETWEEN)))) type of thing.

There are two intermediate steps I've tried to figure out to no avail. I’d imagine they are both solved by the same effective function to get to the end goal of hours, by service area, by resource, by year.

Pivot table to show

• Hours by resource, by year
• Hours by service area, by year

in order to show the end goal of

• Hours, by service area, by resource, by year

You can see the issue in the output below.

Note that when using the total of assigned hours, and the resource name from AssignmentQuery, I get the right sums, but when using any date value … I only get the hours against the start date (the active relationship in the model). What I need is for those hours to be evenly spread across the period that they’re applicable to (so if something has 1,000 hours between 1/1/16 and 1/1/19 I’d expect 333 hours/year to show).

My initial thought is that the selector/filter/calculate function needs to do the following:

• Select the hours for the person
• Select the days in the period filtered to (e.g. month/year/quarter/whatever) from either a filter or as a column header
• Calculate the hours per day
• Get the working days in the filtered period
• select the sum of the hours from the overlap

Any ideas are greatly appreciated! I’m open to doing some additional ETL/data creation as a PowerQuery step, but would really like to figure out the right DAX expression for this so it can be something available as a time-slicer/filter on the project.

Thanks in advance.

** Edit to post the revised version of the answer provided **

[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] )
                                 )
                  )
      )
)

解决方案

Given that you have a relatively complex model in place and your requirement is not totally straightforward, I'm not sure that this will get you all the way there but hopefully it will at least either give you the inspiration to modify it for your purposes or start a more detailed discussion.

The measures below effectively sum the hours, apply them to dates where the dates are between the start and end and divides the total by the number of days. The slight complexity is this needs to be iterated x2 - once over dates and once over rows in the table containing the hours.

An issue for you might be that I'm using an unconnected date table and if you can't replicate this situation in your model then we will need to try using some ALL() functions instead.

Solution below assumes a table called 'data' that has 4 columns: id, start, end, value and table called calendar that has 2 columns Date and Month.

Measure 1: Sum the hours

[Hours] =SUM(Data[Value])

Measure 2: Apply the hours to the dates and divide by number of dates

[Hours Apportioned Raw] =
 CALCULATE ([Hours],
FILTER (
    Data,
    Data[Start] <= MAX ( Calendar[Date] )
        && Data[END] >= MAX ( Calendar[Date] )
       )
            )
/ ( MAX ( Data[End] ) - MAX ( Data[Start] ) )

Measure 3: Iterate Measure 2 over dates and ids to give correct values

=
    SUMX (
        VALUES ( Calendar[Date] ),
        SUMX ( VALUES ( Data[ID] ), [Hours Apportioned RAW] )
    )

Hope this makes some sense, very simple test model here: Test Model

Note you will need to download the model not just view it in the browser.

这篇关于DAX / PowerPivot查询功能用于在时间段内扩展聚合值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！