社群里有朋友提出这么一个问题：
问题
这是原始数据：
这是想要的结果：
结果呈现：
看到这个问题，首先第一感觉，好像是非常简单的，获取每一个合同编号的起始月，然后均摊、求和就可以。
但是仔细一想，并没有那么简单。
解决方案
首先，第一行的2020.1.1-2021.12.31总共处在12个月，然而第二行的2021.1.29-2022.1.29却是占了13个月份。
很明显，全部的合同都是1年期，也就是12个月，不能均摊在13个月。
所以第一个问题就是就要定义到底是哪12个月。
经过一番讨论，确认：如果合同的起始日期为当月的15号之前（含），那么就从当月算起共12个月，如果是15号之后（不含），那么就从下个月算起共12个月。
比如2021.1.29-2022.1.29算作2021.2-2022.1这12个月的；2021.1.14-2022.1.13算作2021.1-2021.12这12个月。
第二个问题：结果的呈现带着“年月”这个维度，因此需要写一个日期表：
Power BI创建日期表的几种方式概览
由于表中只需要一个年月维度，我们简化一下：
日期表 =
GENERATE (
CALENDAR ( DATE ( 2020, 1, 1 ), DATE ( 2022, 12, 31 ) ),
VAR currentDay = [Date]
VAR year = YEAR ( currentDay )
VAR month = FORMAT ( currentDay, "MM" )
RETURN ROW (
"年度月份", year&month
)
) 复制
第三步：写度量值
思路：
- 根据起止日期列出期间的所有日期，并得到所在的月份A
- 由起始日期是否在当月15日之前（含）来确定均摊的月份B
- 通过筛选月份确定当月是否在以上B表中，如果在就返回分摊费用
统统这些，我们都可以直接在度量值中写出来。
- 根据起止日期列出期间的所有日期
var date_begin=SELECTEDVALUE(data[起始日])
var date_end=SELECTEDVALUE(data[终止日])
var date_table=
GENERATE (
CALENDAR ( date_begin, date_end ),
VAR currentDay = [Date]
VAR year = YEAR ( currentDay )
VAR month = FORMAT ( currentDay, "MM" )
RETURN ROW (
"年度", year ,
"月份", month,
"年度月份", year&month
)
) 复制
- 得到所列的日期所在的月份表A
用SUMMARIZE函数来实现。
- 由起始日期是否在当月15日之前（含）来确定均摊的月份表B
var a=TOPN(12,month_table0,[年度月份]*(-1))
//此处用了一个技巧，*(-1)来选择最小的12个月份
var b=TOPN(12,month_table0,[年度月份])
IF(
RIGHT(date_begin,2)<="15",
return a,
return b
) 复制
- 通过筛选月份确定当月是否在以上B表中，如果在就返回分摊费用
使用CONTAINSSTRING和CONCATENATEX来确定所筛选的月份是否在以上的B表中。
将两个维度和度量值拖到矩阵中：
OK！
后记
解决该问题其实可以使用新建列，但步骤上其实都差不多。
最近在研究流数据集，流数据集中是无法通过创建新列或者进行PQ处理的，甚至连数据格式都无法更改，能做的只有创建复杂度量值。
因此对于一些问题，最近是不考虑使用新建列的方式，而只考虑采取新建度量值进行聚合运算来解决。
这样做的好处是，模型会简单轻便一些；但坏处是掉头发会严重，而且虚拟表中的结果你是没法直接看到的，所以写的时候就很难受了。
不过，解决办法也不是没有，可以使用DAX studio，比较直观地看到表，因此最近也频繁使用起了它。