如何在Power BI 里分析《资治通鉴》？顺便解决1900年之前的日期问题

几乎每一个报告中都需要日期表：

Power BI创建日期表的几种方式概览

也有不少报告需要同时使用日期表和时间表：

在PowerBI中创建时间表（非日期表）

如何在PowerBI中同时使用日期表和时间表

并对日期时间序列进行各种分析：

【运营】任意两个时间段的复购率？Power BI一招帮你搞定

这样的Power BI周分析你见过吗？

中国式放假与调休——如何计算平均发货时间？

如何使用Power BI在财年上做周分析？

PowerBI中短小而强悍的相对日期切片器

PowerBI相对日期切片器——解决时区偏差问题

通过"日期偏移"来解决"因中美习惯不同而导致的PowerBI相对日期切片器周分析错误"问题

比如我习惯于使用CALENDAR 和DATE函数来实现日期表的构建：

日期 = CALENDAR (DATE(2015,1,1), DATE(2021,12,31))

学谦建议：对于任意一个函数的理解，我们需谦虚谨慎，做到谨小慎微，认真仔细阅读函数的官方文档：

年份 表示年份的数字。“year”参数的值可以包含一到四位数字 。 根据计算机使用的日期系统解释“year”参数 。支持从 1900 年 3 月 1 日开始的日期。如果输入的数字有小数位，则对该数字执行舍入。对于大于 9999 或小于零（负值）的值，该函数将返回 #VALUE! 错误。如果“年份”值介于 0 和 1899 之间，则该值将与 1900 相加以生成最终值 。 请参下面的示例。 注意： 应尽可能使用四位数的“year”参数，以防止出现意外的结果 。 例如，使用 07 返回 1907 作为年份值。 https://docs.microsoft.com/zh-cn/dax/date-function-dax

里面有两句很重要的话：

1. 只支持从1900年3月1日后
2. 如果年在1900年之前，将返回与1900年相加的结果

纸上得来终觉浅，绝知此事要躬行。我们本着怀疑的态度来试验一下：

比如输入1899年：

诶？这不是有日期么。所以说，官方文档有时候也并不完全可信。

我们不断将值减小，可以发现一直到公元100年都是可以获取日期的：

如果继续往下，就会发现文章中说的，99+1900=1999了：

因此，截止目前，经实验检验，可以由CALENDAR 和DATE函数直接获取的日期为从公元100年1月1日开始。

如果想要获取公元100年之前的日期呢？

说到这里，有同学会问了：大师，请问你什么业务可以追溯到公元100年之前的？

学谦：

首先，PowerBI是一个数据分析的工具，并不仅仅是可以用来进行商业数据分析和商业智能，它还可以用来做科研（一本正经地说）：

学谦也经常拿PowerBI来分析《资治通鉴》（听我给你一本正经的胡说八道）：

《资治通鉴》中记载着大大小小的各个朝代的各种战争，往往这些战争都会清楚地记载开始日期与结束日期，当然是用干支纪年的日期来记载的。但是我们可以根据特定的算法将它们和公历的日期一一对应起来，从而可以计算出每一次战争的延续时间，以分析不同的阶段战争持续时间与人员伤亡情况等。

此时就会出现一个问题，在pq中显示的好好的日期加载到报告中却消失了：

这就不太好了。

所以，办法还是需要想一想的。

我们注意到，文档中还有这么一句话：

对于大于 9999 或小于零（负值）的值，该函数将返回 #VALUE! 错误。

我们仍然本着求真务实的态度来试验一下：

我们输入-1，结果它并没有像文档中说的那样出现错误，而是-1+1990=1899，哇哦。

那么如果我们输入-1899呢？会不会出现公元1年呢？

实践是检验真理的唯一标准：

哇哦！

我们好像解决了一个大问题。

至少从公元1年1月1日开始往后的日期我们都是可以直接用CALENDAR 和DATE函数获取的。

我们再往下尝试一下：

它完整的错误提示是这样的：

System.FormatException: 字符串“-0001-01-01T00:00:00”不是有效的 AllXsd 值。在 System.Xml.Schema.XsdDateTime..ctor(String text, XsdDateTimeFlags kinds) 在 System.Xml.XmlConvert.ToDateTime(String s, XmlDateTimeSerializationMode dateTimeOption)

在 Microsoft.AnalysisServices.AdomdClient.FormattersHelpers.ConvertToDateTime(String s) 在 Microsoft.AnalysisServices.AdomdClient.FormattersHelpers.ConvertFromXml(String xmlValue, Type targetType, Boolean convertLocalTime)

哦，这样就不行了，出现了公元前了。

那，如果大于9999呢？

果然是错误。

结论：

1.本文对PowerBI的DATE函数的官方文档进行了实验检验并修正了其中的一些范围问题。

2.DATE函数能够获取的日期范围为公元1年1月1日-9999年12月31日。

3.PowerBI可以用来做科研

4.PowerBI可以用来分析《资治通鉴》

遗憾：

公元100年之前的日期从pq中加载到报表中消失的问题尚未解决，有待进一步研究。