excel-formula - 如何使用 DAX 在 Power BI 中计算预计 YTD

Question

我在 SSRS 中有一份报告，其中包含我需要转换为 DAX 的计算。目标是计算 ProjectedYTD

但为了做到这一点，我首先必须计算当天的溢价和预算。所以我在 SSRS 中的做法是:

1) 我用当月的总预算除以当月的天数来计算当月的每日预算。所以 10 月份每天的预算是

2,579,757/31 = 83,217.97。

2) 接下来，我将每日预算乘以当月月初到今天之间的天数。所以如果今天是 10 月 27 日那么

83,217.97 * 27 = 2,246,885

3) 最后一步是我将本月(10 月)之前的全部预算添加到 10 月到今天的预算:

23,385,769 + 2,246,885 = 25,632,654

然后我计算每个月的方差和截至今天的总和。

Variance = (Total Premium - Budget till current day)/ Budget till current day * 100

这是:

 (28,477,194 - 25,632,654) / 25,632,654 * 100 = 11.1%

最后计算Projected YTD:

(Total Budget for whole year  -  Budget till current day) * (1 + Variance) + TotalPremium

这是:

(30,148,763 - 25,632,654) * (1 + 0.11) + 28,477,174 = 33,494,470

所以我假设我必须在 Power BI 中创建所有这些变量，然后执行计算。

在 Power BI 中，我看到了类似的东西:

.ipbx 文件可以在这里访问:

https://www.dropbox.com/s/io1yumnqlrzj067/PremiumByDivisions.pbix?dl=0

请帮忙。

更新 1

Joe，非常感谢你抽出时间。我认为它没有正确计算 BudgetTillCurrentDay。在屏幕截图中，它是 25,965,526，这是截至 10 月底的预算总和。

但我需要直到今天。所以如果今天是 30 号，那么 2,579,757/31 * 30 = 2,496,539 -- 这是从 10 月初到今天(10 月 30 日)的预算

然后总和(截至 9 月底的年度总预算)+ 2,496,539 = 25,882,308。

抱歉造成混淆。

Answer 1

在进入我的解决方案之前的一些评论:

• 您共享的文件中的数字与您问题中的数字不完全一致，因此希望此解决方案符合预期的数字。
• 此解决方案末尾有一张屏幕截图，显示了我正在处理的报告页面，以便您可以看到我用作示例的结构。
• 要评论您问题中的最后一句话，从技术上讲，您不必采取所有这些措施；您可以将这些公式合并为一个(类似于我在下面的第一个预算相关公式中所做的)，但我认为像我那样将它们分解成单独的度量将更好地进行报告工作。

---

在使用预算之前，我在 Premiums 表中创建了一些度量，以帮助稍后简化其他公式。

PremiumsMTD = TOTALMTD(SUM(Premiums[Premium]), Dates[Date])

PremiumsYTD = TOTALYTD(SUM(Premiums[Premium]), Dates[Date])

首先，为了创建一个月至今的预算指标，我使用了以下公式。这个公式一步一步地 1) 算出当月的总天数，2) 用当月的总预算除以天数，3) 算出当月已经过去了多少天(所有如果是过去的一个月，如果是 future 的一个月，则为 0，当月的当前日期)，最后 4) 将每天的预算乘以过去的天数。

BudgetMTD = 
VAR DaysOfMonth = MAXX(Dates, DAY(EOMONTH(Dates[Date], 0)))
VAR BudgetPerDayForMonth = SUM(BudgetData[Amount]) / DaysOfMonth
VAR DaysInMonthToToday = MAXX(Dates,
    IF(Dates[Date] < TODAY(), DAY(Dates[Date]),
        IF(Dates[Date] > TODAY(), 0,
            DAY(TODAY())
        )))

RETURN BudgetPerDayForMonth * DaysInMonthToToday

接下来，对于 YTD，只需使用 Power BI 的内置 TOTALYTD 函数(就像对 Premiums 度量所做的那样)，将新创建的 BudgetMTD 度量作为表达式并将其过滤到非 future 日期.

BudgetYTD = TOTALYTD(BudgetData[BudgetMTD], Dates[Date], Dates[Date] <= TODAY())

然后您可以通过使用之前创建的 MTD 和 YTD 度量进行简单的数学计算来获得差异。

BudgetVarianceMTD = (Premiums[PremiumsMTD] - BudgetData[BudgetMTD]) / BudgetData[BudgetMTD]

BudgetVarianceYTD = (Premiums[PremiumsYTD] - BudgetData[BudgetYTD]) / BudgetData[BudgetYTD]

这是我正在处理的报告页面。请注意，我使用的是一个矩阵(虽然它可以有许多不同的视觉效果)，以​​ MonthName 作为轴/类别。由于视觉对象是一个月粒度的，因此 YTD 值在同一视觉对象中没有合适的位置存在(Power BI 自动执行的小计不会与自定义度量匹配)，这就是为什么有卡片的原因YTD 值。