这两天为一个表筛选的问题头疼，先抽个身来总结下这段时间一直和对战的小婊砸=> « 日期 »。
日期处理可以说是数据处理中最让人心烦的一部分，经常在不明所以的情况下影响其他数据的运算。尤其是财务数据，销售数据，嗯，所以，在Power BI里建立一个时间表，再开启你的分析生涯吧。
在我刚入门Power BI的第2周，公司就开启了对我的碾压，需要给客户做一份动态报表，主要内容是从日期角度分析业务指标。
需要分析的指标有，Chiffre d’affaire ,Marge, Taux de Marge, Taux de Transformation… 总而言之就是销售额利润利润率客户转化率订单数量客户流量等等等，我能想到和想不到的指标全部都有 ，然后从日期角度分析业务指标 :当天，当周，当月，当极度，当年，最近12个月以及和去年同日，同期的比较。作为一个小白，实在是怕怕怕怕━((*′д｀)爻(′д｀*))━!!!!
痛苦的数据筛选/建模/以及各项指标计算过程夹杂着我和DAX的缠绵情缘就先不说了，建立了指标集之后，我终于要开始给这些指标穿上时间的外衣了!!!!
1时间智能
DAX 提供时间智能分析函数Time Intelligence，(https://msdn.microsoft.com/en-us/query-bi/dax/time-intelligence-functions-dax)需要在PowerBI里创建一张日期参照表进行后续，其实可以选择DAX公式或Power Pivot公式 ，我用了dax，公式为
Dim_Date = CALENDAR( 开始日期，结束日期)
我的日期区间选择范围是 从今天一直往前推到12个月，这个时间段同时也是我的销售表中销售数据的最大和最小日期。所以，选取了销售表内的最大和最小日期作为日期表的起止，DAX公式为:
CALENDAR(MINX(VentesTicket;VentesTicket[DateVentes]);MAXX(VentesTicket;VentesTicket[DateVentes]))
有了单列日期，还需要不同的时间粒度 :日/周/月/季/年等，参考文章(https://www.cnblogs.com/wenBlog/p/5692658.html)
此外，如果需要不同时间段的销售指标，例如每天 1点 /2点/3点 … 每个小时的销售额等，有两种方法实现:
第一种，在时间参考表的日期键值中加入时刻信息 DAX公式如下:
DateTimeTable=SELECTCOLUMNS(
CROSSJOIN(
//************************************************************
// Date range below. This will generatea table with a [Date] column
//************************************************************
CALENDAR(DATE(2018;01;01);DATE(2018;01;02));
//************************************************************
// Time granduality below. This will generate a table with a [Time]column
//************************************************************
SELECTCOLUMNS(
CROSSJOIN(
SELECTCOLUMNS(GENERATESERIES(0;59;1);"Second";[Value]);
SELECTCOLUMNS(GENERATESERIES(0;59;1);"Minute";[Value]);
SELECTCOLUMNS(GENERATESERIES(0;23;1);"Hour";[Value])
);
"Time";TIME([Hour];[Minute];[Second])
)
);
//************************************************************
// Custom table columns below.
// This is extracted from the Date and Timecolumns generated above
//************************************************************
"Date"; FORMAT([Date];"YYYY-MM-DD");
"Time"; FORMAT([Time];"hh:mm:ss");
"DateTime"; [Date]+[Time]
)
然后对每个日期键值进行分组 IF… ELSE…
第二种，在销售数据表格里对每一条销售记录进行分组，再建立一个时间段的参考表格
一个小插曲，关于Weeknumber，power bi中欧洲的日期 (从周一开始算一个周的开始)，2017年1月1号被单独列为第一周，而从2017年1月2号开始算做第二周，所以在计算周粒度的指标时，要注意对去年Weeknumber的选择，解决方法:
Dax 公式
SemaineN-1 =
VAR CurrentWeek = SELECTEDVALUE (Dim_Date[WeekNumber])
VAR CurrentYear = SELECTEDVALUE (Dim_Date[Année])
VAR CurrentDay =SELECTEDVALUE(Dim_Date[DayOfWeekFull])
RETURN
CALCULATE([SemaineN];
(
FILTER( ALL (Dim_Date);
Dim_Date[DayOfWeekFull]=CurrentDay&& Dim_Date[WeekNumber]=CurrentWeek+1 && Dim_Date[Année]=CurrentYear-1)))
或者,自己在excel中建立一个正确的日期表导入
2 日期格式的处理
将数字列转换成时间列有些原始数据对时间的存储采用了纯数字形式，例如用20170122表示2017年1月22日。但是采用这种存储方式的数据列在导入到Power BI中会被认为是整数类型，将无法自动转换成日期格式，就不能做时间相关运算。要解决该问题，有三种思路，一种是在数字形式的日期上添加反斜杠“/”，将年月日分开。例如将20170122变成2017/02/22，之后就能被Power BI自动识别转换成日期格式了。转换方式可以用一下DAX完成:
DateFormat = LEFT(Store[Date],4)&"/"&MID(Store[Date],5,2)&"/"&RIGHT(Store[Date],2)
另外一种方式是调用DATE函数，直接将数字类型转换成日期时间类型。
DateTime = DATE(INT(LEFT(Store[Date],4)),INT(MID(Store[Date],5,2)),INT(RIGHT(Store[Date],2)))
第三种，先转换成text类型再转换为date类型。
3 神奇的Format函数
极好的参考博客:
http://www.gnu.org/software/pspp/manual/html_node/Time-and-Date-Formats.html
https://blog.csdn.net/jessica_seiya/article/details/80666171
如果要显示秒 mm，不能用Format ( Datekey ; mm) 而是 h :mm
4 由于地区的不同日期的格式会有所不同
Power BI 关于日期显示格式的那些事儿 (https://blog.csdn.net/jessica_seiya/article/details/81778734)
在日期上，到目前为止遇到这些问题，以后有新的问题会添加进来啦。
我去继续思考让我头疼的问题了ヾ(￣▽￣)Bye~Bye~
网友评论