DAX / Power Pivot：从累计总计中计算75％的到期日期 [英] DAX/Power Pivot: Calculate 75% Expended Date from Cumulative Total

问题描述

我有三个包含成本的表：预测成本，实际成本，发票成本。每个单位都有一个 EAC过滤器列，用于确定是否在完成时估算中包含费用，该列会随时间和/或随着添加数据而自动更改。以下是示例：

I have three tables that contain cost: Forecasted Cost, Actual Costs, Invoiced Costs. Each has an "EAC Filter" column for a Y/N of whether to include the cost in an Estimate at Completion, which automatically changes over time and/or as data is added. Here are examples:

三个表中的EAC可以如下计算：

EAC from the three tables can be calculated as follows:

Total Cost = Sum(Forecast[Cost])+Sum(Actual[Cost])+Sum(Invoice[Cost])
EAC = Calculate([Total Cost],EAC_Filter[EAC Filter]="Y")

我的预算在帐户级别，也可以汇总

I have a budget at the "Account" level, which could also be rolled up to a "Dept" level budget.

我需要为我的Power Pivot表度量，该表将使用某种累计成本结合使用以下各项来显示超出或预计超出预算预算的那一周：累计费用> = .75 *预算的最大周数。

I need a measure for my Power Pivot table which will display the week at which costs have exceeded, or are forecasted to exceed 75% of the budget, using some sort of a cumulative cost, combined with the max week where cumulative cost >= .75 * Budget.

该周在一年中的编号如下：

The weeks are numbered through the year as follows:

感谢您的帮助！

推荐答案

给我一​​个 EAC 确保每周总费用，

Given an EAC measure which sums the cost per week,

EAC = CALCULATE(SUM(Forcast[Cost]) + SUM(Acutal[Cost]) + SUM(Invoice[Cost]),
                EAC_Filter[EAC Filter] = "Y")

您可以按以下方法创建累积成本度量：

You can create a Cumulative Cost measure as follows:

Cumulative Cost = CALCULATE([EAC],
                      FILTER(ALL('Calendar'), 'Calendar'[Week] <= MAX('Calendar'[Week])))

使用此方法，我们可以创建一个指标来预测当周费用超过预算的75％：

Using this, we can create a measure that predicts the week the cost exceeds 75% of the budget:

75% Week = MINX(FILTER(ALL('Calendar'), [Cumulative Cost] > 0.75 * SUM(Budget[Budget])),
               'Calendar'[Week])

---

这是关系结构的样子：

---

Here's what the relationships structure looks like:

这篇关于DAX / Power Pivot：从累计总计中计算75％的到期日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！