在DAX中进行度量以仅针对Power BI计算选定月份的年初至今 [英] Measure in DAX to calculate YTD for chosen month only for Power BI

问题描述

如何构造DAX度量以计算特定月份的YTD值之和？

在这里，我们将FactTable按月分组。 FactTable填充了实际数据和预测数据。知道实际结束时间的唯一方法是[年初至今]列中的[日期截止]表中的信息。在[年初至今]列中的[日期截止]表中-这是一个单值表–我们有一个有趣的选定月份，我们要查看其年初至今的计算。在我们的情况下是三月。 FactTable每月都会不定期更新，通常会延迟一个月。由于不定期更新，无法将其链接到今天的时间函数。

我们希望在[YTD年末]的黄卡视觉中显示正确的YTD值。当我们单击 2018-03上的切片器时，我们几乎得到了所需的值–黄卡中的正确值为66。但是，此解决方案不是自动的。我想在[YTD结束]月更改时自动看到正确的值，在本例中为4月，然后为5月。我不希望用户完成此操作。

我的绝望之举可以从文件中下载：

我建议您更改计算在某些情况下避免遗漏值的措施：

 总计= SUM（FactTable [Value]）
 MTD = TOTALMTD（[[Total]，'Calendar'[Date]）
年初至今= TOTALYTD（[Total]，'Calendar'[Date]）
  

---

更新：

对我来说，您现在想要实现的目标要清晰得多，但似乎仍然

对于年初至今结束视觉效果，您可以使用供您参考。

---

再次更新：

我认为您必须编写自己的 YTD 计算而不是使用内置的日期，以便您可以使用在另一个表中定义的截止日期。在这里，我假设您在截止日期 [年初至今] 中只有一行。请注意，我已经在过滤器中添加了 ALL（），以便在单击某些其他行/过滤器时，黄卡保持不变（66）而不显示空白：

  YTD_Special = 
 CALCULATE（
 [total]，
 FILTER（
 ALL（FactTable），
 FactTable [Date]> = DATE（YEAR（VALUES（'Cut off date'[YTD End]）），1、1）&& 
 FactTable [Date ]< = VALUES（'截止日期'[年初至今]）
）
）
  

How to construct DAX measure to calculate sum of YTD value for specific month?

Here we have FactTable grouped by months. FactTable is filled with both Actual data and Forecast data. The only way to know when Actual end is information in table [Cut of date] in column [End of YTD]. In table [Cut of date] in column [End of YTD] – it is a single value table – we have the interesting chosen month, for which we want to see the calculation of YTD. In our case it is March. FactTable is updated irregularly every month with usually one month delay. There is no way of linking it to time functions like TODAY because of irregular update.

We would like to have a correct value of YTD displayed in yellow Card Visual for the month [End of YTD]. When we click on the slicer on "2018-03" we get almost what we want – correct value of 66 in the yellow Card. However this solution is not automatic. I want to see correct value automatically when the [End of YTD] month changes, in our case to April or then to May. I do not want it done by user.

My desperate effort can be downloaded from file: DAX YTD.pbix

I pursued the deer in various ways:

1. By using FILTER function in DAX measures. But it seems that the FILTER function is to harsh. It is applied to fact table first, selecting only one month, and then calculating YTD value wrongly. So if there would be any option for forcing order of calculation and filtering, there would be hope.
2. I tried SWITCH function to display proper result for specific month and 0 or null for other months. Although I succeed in this, I was not able to take advantage of it. When it came to filtering I was as hopeless as before. BTW I would be able to make it if SWITCH produced totals at the end of the table, but it does not. Surprisingly.
3. I put some hopes in RELATED function to display proper results in the [Cut off date] table. I have not walk out of the fog so far.

I would appreciate your help.

Update before bounty. Going to higher level. I have introduced a Category column to FactTable. Please download DAX YTD by category.pbix. So filtering gets more complex now. I would like to have correct YTD figures for Apples category.

解决方案

Did you use the Date column from the Calendar table, instead of the one from FactTable?

If you use the date column from FactTable, when you apply a filter on the date, it will filter on the fact records which is in March, and then do the calculation afterwards, hence the result 33.

If you use the one from Calendar, when you apply a filter on it, it filters the records on Calendar (which you want to show in the chart), so the underlying calculation will still remain intact.

---

A working example:

Calendar = CALENDAR(DATE(2010, 1, 1), DATE(2020, 12, 31))

I suggest you to change the calculations of the measures to avoid missing values in some cases:

Total = SUM(FactTable[Value])
MTD = TOTALMTD([Total], 'Calendar'[Date])
YTD = TOTALYTD([Total], 'Calendar'[Date])

---

UPDATE:

It's much clearer to me what you want to achieve now but it still seems an XY problem to me.

I understand that you want to show the dashboard as is so that users do not need to click/input every time to see what they are supposed to see. That's why I don't get why you need to create a new table to store the Cut off date (End of YTD). How is it going to be maintained automatically?

The relative date filtering solution above actually still works in the .pbix file you've shared. If you drag the Date column from the Calendar table to visual level filters for the yellow card and add the relative date filtering, it should work as below:

For the End of YTD visual, you can use the following measure to get the first day of last calendar month, so you don't need to create another table for it:

End of YTD = EOMONTH(TODAY(), -2) + 1

And hopefully this is what you want to achieve:

Updated file for your reference.

---

UPDATE again:

I think you'll have to write your own YTD calculation instead of using the built-in one, so that you can make use of the cut off date you defined in another table. Here I assume that you have one and only one row in 'Cut off date'[End of YTD]. Note that I've added ALL() to the filter, so that the yellow card remains the same (66) instead of showing blank when some other rows/filters are clicked:

YTD_Special = 
CALCULATE(
    [Total],
    FILTER(
        ALL(FactTable),
        FactTable[Date] >= DATE(YEAR(VALUES('Cut off date'[End of YTD])), 1, 1) &&
        FactTable[Date] <= VALUES('Cut off date'[End of YTD])
    )
)

这篇关于在DAX中进行度量以仅针对Power BI计算选定月份的年初至今的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！