DAX/PowerPivot 查询函数可在一段时间内分布聚合值 [英] DAX / PowerPivot query functions to spread aggregated values over time period

问题描述

我正在尝试计算 DAX 表达式 [对于 Excel 2010 中的 MS PowerPivot] 以在其应用的范围内均匀分布值的总和，并在给定的时间跨度/周期内重新计算总和.在 SQL Server 中交叉应用是微不足道的，尽管每次尝试都以同样的错误结果结束.

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.

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

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.

模型看起来像这样:

我在 ResourceQuery、TaskQuery 和 AssignmentQuery 表(均直接取自 MS Project 输出)和 ServiceAreaQuery(来自 TaskQuery 的唯一值……本质上是子项目)上创建了计算列.每个还有一个简单的度量，即分配的小时数列的总和.

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.

数据本身看起来像您希望从 Project 2010 文件中获得的数据，并且具有 {start_date}、{finish_date} 和小时数.任务的日期可以跨越 1 天到 5 年不等……这就是我的问题所在.

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?

即使我使用日期表中的年份列，时间智能也无法捕捉到它&对于 CALCULATE(SUM(FILTER(COUNTROWS(DATESBETWEEN)))) 类型的事情，我已经没有想法了.

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.

要显示的数据透视表

• 按资源的小时数，按年
• 按服务区域的小时数，按年

为了显示的最终目标

• 小时数，按服务区域，按资源，按年

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

You can see the issue in the output below.

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

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:

• 选择此人的营业时间
• 从过滤器或列标题中选择过滤为(例如月/年/季度/其他)的期间中的天数
• 计算每天的小时数
• 获取过滤期间的工作日
• 从重叠中选择小时数的总和

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

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.

提前致谢.

** 编辑以发布所提供答案的修订版 **

** 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.

下面的度量有效地将小时数相加，将它们应用于日期介于开始和结束之间的日期，并将总数除以天数.稍微复杂的是，这需要迭代 x2 - 一次遍历日期，一次遍历包含小时的表中的行.

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.

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

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.

下面的解决方案假设一个名为数据"的表有 4 列:id、开始、结束、值和名为日历的表，其中有 2 列日期和月份.

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.

措施 1:合计小时数

[Hours] =SUM(Data[Value])

措施 2:将小时数应用于日期并除以日期数

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

措施 3:在日期和 ID 上迭代措施 2 以给出正确的值

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屋！