Power BI之DAX神功：第2卷第15回 累计至今区间、Filter与CalculateTable区别
一、理解累计至今的区间计算原理
《DAX神功》第2卷第11回 我们学习了累计至今区间，现在我们研究一下它们的计算过程
【1】DatesYTD 年初至今
思路：
1.确定最大日期 2021/7/4
2.确定最大日期所在年：2021年
3.确定小于等于最大日期且等于2021年的数据
【新建表】
年初至今 = FILTER(ALL('Sheet1'[日期]),YEAR('Sheet1'[日期])=year(MAX('Sheet1'[日期])) && 'Sheet1'[日期]<=MAX('Sheet1'[日期]))
// 结果 2021/1/1~2021/7/4
【2】DatesMTD 月初至今
思路：
1.确定最大日期 2021/7/4
2.确定最大日期所在年：2021年
3.确定最大日期所在月：7月
4.确定小于等于最大日期且等于2021年7月的数据
【新建表】
月初至今 = FILTER(ALL('Sheet1'[日期]),YEAR('Sheet1'[日期])=year(MAX('Sheet1'[日期])) && MONTH('Sheet1'[日期])=MONTH(MAX('Sheet1'[日期])) && 'Sheet1'[日期]<=MAX('Sheet1'[日期]))
// 结果 2021/7/1~2021/7/4
【3】DatesQTD 季初至今
思路：
1.确定最大日期 2021//7/4
2.确定最大日期所在年：2021年
3.确定最大日期所在季度：3季度
4.确定小于等于最大日期且等于2021年3季度的数据
【新建表】
季初至今 = FILTER(ALL('Sheet1'[日期]),YEAR('Sheet1'[日期])=year(MAX('Sheet1'[日期])) && QUARTER('Sheet1'[日期])= QUARTER(MAX('Sheet1'[日期])) && 'Sheet1'[日期]<=MAX('Sheet1'[日期]))
// 结果 2021/7/1~2021/7/4
【4】DatesYTD第二参数计算规则
《DAX神功》第2卷第11集 我们讲过 只有DatesYTD拥有第二参数（可选），MTD和QTD是没有第二参数的。
【新建表】年初至今 = DATESYTD('Sheet1'[日期],"6-30") // 返回2021/7/1~2021/7/4
思路：
1.找到最大日期：2021/7/4
2.指定6月30日：DATE(year(2021/7/4),6,30)
3.筛选日期大于DATE(year(2021/7/4),6,30)和日期小于等于最大日期的
年初至今 = filter(ALL(Sheet1[日期]),'Sheet1'[日期]>date(year(max('Sheet1'[日期])),6,30) && 'Sheet1'[日期]<=MAX('Sheet1'[日期]))
PS:本人没有做过财务，也没有在外企工作过，本人经历的机关单位均是1月1日和7月1日起计算。对其它机构没有工作经验，很难顾及全面，请见谅。
二、为什么要用Filter+ALL('Sheet1'[日期])
ALL('Sheet1'[日期]) 将Sheet1中日期列所有行去重后返回成一张表
《DAX神功》第2卷第6回 RankX神秘的第三参数中有详细
如果单纯为了筛选表，VALUES与DISTINCT函数也是可以的。
三、Filter是迭代函数，如何进行上下文转换？
【新建列】
列1 = countrows(DATESYTD('Sheet1'[日期]))
列2 = COUNTROWS(FILTER(ALL('Sheet1'[日期]),YEAR('Sheet1'[日期])=year(MAX('Sheet1'[日期])) && 'Sheet1'[日期]<=MAX('Sheet1'[日期])))
列1使用了时间智能日期函数DATESYTD，它可以转换上下文，但是Filter不行，所以我们给Filter嵌套CalculateTable
列3 = COUNTROWS(CALCULATETABLE(FILTER(ALL('Sheet1'[日期]),YEAR('Sheet1'[日期])=year(MAX('Sheet1'[日期])) && 'Sheet1'[日期]<=MAX('Sheet1'[日期]))))
从上面的公式，我们推导出：
DatesYTD(表[日期]) 等价于 CalculateTable(Fliter(all(日期列),年=max年 && 日期<=Max日期 )
// 当Filter套上了CalculateTable时，就具备了筛选功能，就好比我们给聚合函数加Calculate一样，因为Filter返回的是表而不是一个值，所以从行上下文转换成筛选上下文需要使用CalculateTable
【新建列】
列1 = sum(Sheet3[数量])
列2 = CALCULATE(SUM(Sheet3[数量]))
四、CalculateTable与Filter在筛选表上有什么区别
【1】使用all函数后的区别
【新建表】表1 = FILTER(all('Sheet4'),'Sheet4'[性别]="女")
// filter中使用all不会影响筛选，《DAX神功》第2卷第3回 计算移动平均值再次理解Filter+ALL 已经得到了证明
【新建表】表2 = CALCULATETABLE(all('Sheet4'),'Sheet4'[性别]="女")
// 但是当ALL放在Calculate或CalculateTable引擎中，ALL是调节器的作用，表2的意思是筛选女生并返回Sheet4这张表的所有行。等于没筛选
【2】不使用all函数时，筛选表从表面上看是一样的
【新建表】表1 = FILTER('Sheet4','Sheet4'[性别]="女")
【新建表】表2 = CALCULATETABLE('Sheet4','Sheet4'[性别]="女")
【3】透过现像看本质，二者区别在于计算顺序
【修改新建表】
表1 = FILTER(ADDColumns('Sheet4',"数量",CountRows('Sheet4')),'Sheet4'[性别]="女")
【修改新建表】
表2 = CALCULATETABLE(ADDColumns('Sheet4',"数量",CountRows('Sheet4')),'Sheet4'[性别]="女")
为了弄清楚这件事，我们先看一下ADDColumns生成的结果：
【新建表】表3 = ADDColumns('Sheet4',"数量",CountRows('Sheet4'))
我们来模拟filter和CalculateTable的计算规则：
其实很好理解，Filter先执行第1参数表，再执行第2参数筛选表。而CalculateTable同Calculate一样，先执行筛选器，再执行计算器，只不过在CalculateTable中计算器是第1参数表。
《孙兴华讲PowerBI火力全开》PowerBI必学课程
https://www.bilibili.com/video/BV1qa4y1H7wp
《DAX神功》文字版合集：
https://www.bilibili.com/read/readlist/rl442274
《DAX神功》视频版合集：
https://www.bilibili.com/video/BV1YE411E7p3
PowerBI(DAX函数)、PowerQuery(M函数)、Python办公自动化、Python爬虫、Python数据分析、ExcelVBA、WordVBA、AccessVBA、MySQL等等
