如何使用DAX在Power BI中计算预计年度累计（Projected YTD）

Question

我有一个带有计算的SSRS报告需要翻译成DAX语言。 目标是计算ProjectedYTD。

但是，为了实现这一点，首先我必须通过当前日期计算保费和预算。在SSRS中，我采取的方法是： 1) 我通过将本月总预算除以本月天数来计算每天的预算。因此，十月份的每日预算为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

乔，非常感谢您的时间。 我认为BudgetTillCurrentDay的计算不正确。在截图上是25,965,526，这是直到十月底的预算总和。

但我需要到今天。所以如果今天是30日，那么2,579,757 / 31 * 30 = 2,496,539 - 这是从10月初到今天（10月30日）的预算。 然后是整个年度截至9月底的预算总和 + 2,496,539 = 25,882,308。

抱歉造成困扰。

- Serdia

Answer 1

一些在提出我的解决方案之前的注释：

• 您分享的文件中的数字与您的问题中的数字并不完全对齐，因此希望此解决方案与预期的数字相匹配。
• 这个解决方案结束时有一个屏幕截图展示我正在工作的报告页面，以便您可以看到我使用的结构作为示例。
• 针对你问题中的最后一句话，技术上讲，您不必制作所有这些度量值； 您可以将公式合并为一个（类似于我下面的第一个与预算相关的公式所做的），但我认为像我所做的那样将它们分成单独的度量值会更适合报告工作。

---

在处理预算之前，我在保险费用表中创建了几项措施，以帮助简化后续的公式。

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

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

首先，为了创建一个月度预算测量，我使用了以下公式。逐步地，这个公式 1) 计算出本月的总天数，2) 将本月总预算除以天数，3) 计算出本月已经过去了多少天（如果是过去的月份，则计算全部天数；如果是未来的月份，则计算零天数；如果是当前月份，则计算截至当前日期的天数），最后 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函数（就像对保费度量值所做的那样），将新创建的BudgetMTD度量值作为表达式进行过滤，以排除未来的日期。

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值的卡片。

- Joe Gravelyn