daxpowerbi⽣成表函数_PowerBI应⽤时间智能（⽣成⽇期
表）
简介
Power BI Desktop -是⼀款由微软发布的⾃助式商业智能⼯具，功能强⼤、易于使⽤。其中还可以通过微软云连多个数据源并且使⽤数据源来创建可视化表盘。
但是⼏乎所有的BI都需要展⽰如何随时间改变KPI。因此我将会介绍⼀个帮助我们使⽤事件元素来分析数据的关键功能。在PowerBI Desktop 中叫做“time intelligence”。应⽤这种时域分析法能是商业智能中基本的数据表现形式。毕竟公司想要知道的⽆⾮就是今年的业绩相⽐去年如何以及取得了何种进步。
“Time intelligence”将需要⼀个⽇期表，花费⼀定的时间去创建⼀个成功数据模型的核⼼就是这个表。然后⽇期表必须与主数据中随时间变化的⽇期字段进⾏关联。需要作如下⼏种事情：
YearToDate, QuarterToDate, 和MonthToDate 的计算
⽐较之前的年、季、⽉
回滚⼀段时间的聚合，⽐如最近三个⽉的累加。
⽐较平⾏时间段，⽐如与之前⼀年相同的⽉份。
当使⽤随时间进⾏的数据分析的时候，很可能要使⽤DAX函数。为了更好地理解，我们将介绍如何创建⽇期表，然后看⼀下⼏种不同的分析时间的计算，最后加⼊这些类型道数据模型中。为了测试我会使⽤⼀个excel作为PowerBI Desktop 的⽂件数据源。
创建并且应⽤⽇期表
对于智能时间，⾄少需要⼀个包含不间断时间范围的⽇期表，并且开始时间的最⼩值是源数据中的最⼩⽇期，结束⽇期⾄少等于源数据中的最⼤值。实践中，需要创建⼀个表，开始⽇期是最早⽇期的1⽉1⽇⽽最⼤⽇期应该是数据源⽇期的上⼀年的12⽉31⽇。⼀旦你创建了这个表，就能连接数据模型中的含有时间字段的表，然后拓展时间相关的分析函数。
1.创建⽇期表
应⽤时间的前提就是创建⽇期表。下⾯步骤说明使⽤DAX创建表的过程：
1 - 打开PowerBI Desktop⽂件C:\PowerBiDesktopSamples\PowerBIDataModel.Pbix.
2 - 切换左侧的tab，选择第⼆个Data如下图所⽰。
3 - 点击左上⽅的Modeling按钮，然后点击新建表按钮。表达式“表=”将出现在公式栏⾥⾯，
4 - 把Table替换成DateDimension
5 - 输⼊DAX函数CALENDAR("1/1/2012","31/12/2016")，然后回车或者对勾。前⼀个时间是dates表的开始时间，后⼀个时间是结束时间，公式栏内容：DateDimension = CALENDAR( "1/1/2012", "31/12/2016" ).
6 - 回车后，创建了⼀个单列表，表中的内容就是2012-01-01到2016-12-31，所有⽇期。column函数的使用
7 - 编辑表头，改列名称为DateKey，结果如下所⽰：
8 - 点击添加新列按钮或者右键添加新列，新列将会出现在现存列右侧。
9 - 在公⽰栏输⼊“FullYear = YEAR([DateKey])”。
10 -  再添加如下19个列公式。如下：
列标题
公式
注释
VALUE(Right(Year([DateKey]),2))
取后两位数字年
MonthNumberFull
FORMAT([DateKey], "MM")
⽉份取两位数，不⾜的前⾯补0
MonthFull
FORMAT([DateKey], "MMMM")
⽉份展⽰名称
WeekNumber
WEEKNUM([DateKey])
以下⾃⾏测试
MonthAbbr
FORMAT([DateKey], "MMM")
WeekNumberFull
FORMAT(Weeknum([DateKey]), "00")
DayOfMonth
DAY([DateKey])
DayOfMonthFull
FORMAT(Day([DateKey]),"00")
DayOfWeek
WEEKDAY([DateKey])
DayOfWeekFull
FORMAT([DateKey],"dddd")
DayOfWeekAbbr
FORMAT([DateKey],"ddd")
ISODate
[FullYear] & [MonthNumberFull] & [DayOfMonthFull] FullDate
[DayOfMonth] & " " & [MonthFull] & " " & [FullYear] QuarterFull
"Quarter " & ROUNDDOWN(MONTH([DateKey])/4,0)+1 QuarterAbbr
"Qtr " &ROUNDDOWN(MONTH([DateKey])/4,0)+1
"Q" &ROUNDDOWN(MONTH([DateKey])/4,0)+1
QuarterNumber
ROUNDDOWN(MONTH([DateKey])/4,0)+1
QuarterAndYear
DateDimension[Quarter] & " " & [FullYear]
MonthAndYearAbbr
DateDimension[MonthAbbr] & " " & [FullYear]
QuarterAndYearNumber
[FullYear] & [QuarterNumber]
YearAndWeek
VALUE([FullYear] &[WeekNumberFull])
YearAndMonthNumber
Value(DateDimension[FullYear] & DateDimension[MonthNumberFull])
展⽰如下：
创建所有这些表现时间的的⽬的就是早晚有⼀天会⽤到这些⽇期来展⽰报表、聚合指标、展⽰数据。任何有时间元素的表都可以按照这个新增表中的时间转换聚合来可视化数据。这⾥你不需要担⼼是否需要额外的列，因为还可以动态添加你需要的时间元素。
在⽇期表中引⼊列排序
现在需要看⼀下如何排序。典型的例⼦就是⽉份排序。如果你打算展⽰MonthFull 或者MonthAbbr 列，那么将看到⽉份(month)出现在轴标签⾥⾯或者按字母排序的列⾥⾯。
为了避免最后再去调整⽇期表，可以通过应⽤特定的⽇期元素来排列其他列，如下:
1 - 点击打算使⽤其他的列来排序的列(⽐如Monthfull) ；
2 - 点击Modeling下⽅的排序按钮，其他列的名称将会出现，如下图所⽰：
3 - 选择打算按照排序的列(MonthNumber);
这⾥并不能⽴即显⽰出任何不同，但是当在仪表盘中使⽤任何你已经调整过的⽇期列时，它们将会根据排序列进⾏数据排序。
下表提供给你需要的信息来扩展你创建的数据表以便于所有的⽇期元素都能被正确排序。
Column
Sort By Column
MonthAbbr
MonthNumber
DayOfWeekFull
DayOfWeek
DayOfWeekAbbr
DayOfWeek
Quarter And Year
QuarterAndYearNumber
FullDate
DateKey
MonthAndYearAbbr
YearAndMonthNumber
MonthAndYear
YearAndMonthNumber
⽇期表技巧
当引⼊时间智能后，⼀定要遵守两个基础原则。
⽇期范围必须是连续的。
在数据模型中数据范围⼀定是包含所有使⽤的其他表中的⽇期。
⼀旦你知道你数据中的最⼤值和最⼩值⽇期就可以使⽤CALENDAR来⽣成⽇期，即使两个值在不同的表⾥⾯如下：
DateDimension = CALENDAR(MIN(‘Stock‘[PurchaseDate]), MAX(‘Invoices‘[InvoiceDate]))
或者，你可能更喜欢⽇期维度通过全年的数据，在这种情况下，公式可以这样创建表：
DateDimension = CALENDAR(STARTOFYEAR(MIN(‘Stock‘[PurchaseDate])),
ENDOFYEAR(MAX(‘Invoices‘[InvoiceDate])))
这个公式扩展了DAX的计算，两个计算年的公式也是极其有帮助的：
StartOfYear() - 这个公式得出最⼩的年份。
EndOfYear() - 这个公式得出最⼤的年份
注意
这种⽇期范围的主要优点在于随着数据源的变化⾃动更新。因此如果Stock 或 Invoices表数据源扩展了新的数据并且在原有⽇期外的，那么这个时间维度表也会⾃动变化来包含这部分新增数据的部分。
这⾥的可以给⼤家⼀个⼩技巧，不需要每次都去创建这个⽇期表，可先创建⼀个空的模型，⾥⾯只有⽇期表，结束和开始⽇期是⼿填写的，然后加⼊所有其他列，接下来复制这个模板⽂件，以后每次使⽤都
以这个模板⽂件为基础创建。只需要替换⼿动填写的⽇期即可。
向数据模型中加⼊⽇期表
现在你有了⼀个⽇期表，可以与你的数据模型进⾏整合以便于开始应⽤这些智能时间。
1 - 点击关系视图的图表来展⽰数据模型中的表
2 - 点击管理关系按钮，对话框会出现。
3 - 点击新建按钮，创建关系。
4 - 在对话框顶部选择时间维度表。
5 - 点击DateKey列选择。
6 - 在时间维度表下⾯的下拉框中选择Invoice表。
7 - 再点击InvoiceDate列选择，对话库如下：
8 - 点击Ok，新的表关系就建⽴了
9 - 点击关闭，时间维度表就与Invoice表建⽴了关系。
注意为了时间智能在PowerBI中能够正确使⽤⼀定要保证⽇期表和数据表中的数据类型是date或者datetime。
应⽤时间智能
所有的准备⼯作都已经完成了，接下来就看如何使⽤DAX实现随着时间变化来计算指标。
YearToDate, QuarterToDate, 和MonthToDate 运算
⾸先，让我们解决⼀个简单但是频繁的需求：计算⽉累计、季度累计、和年累计的销售数字。
这个例⼦中三个函数是很相似的。因此我只解释第⼀个⽉累计，然后创建下⾯两个⽤复制黏贴的⽅式。
1 -  在数据视图中选择Invoices表，然后点击新建测量；
2 -  在公式栏⽤MonthSales替换Measure ；
3 - 输⼊这个公式MonthSales = TOTALMTD(SUM(InvoiceLines[SalePrice]),DateDimension[DateKey])。
MonthSales = TOTALMTD(SUM(InvoiceLines[SalePrice]),DateDimension[DateKey])
4 - 回车或者点击对勾完成公式
现在可以复制这个公式，⽣成两个新的公式⽤来表⽰季度销售和年度销售⽇期公式如下:
QuarterSales = TOTALQTD(SUM(InvoiceLines[SalePrice]),DateDimension[DateKey])
YearSales = TOTALYTD(SUM(InvoiceLines[SalePrice]),DateDimension[DateKey])
三个公式分别使⽤了Totalmtd()、Totalqtd和Totalytd来聚合时间其他参数是⼀样的。
结果如下：
正缩减，每个⽉的销售数字随着累加到季度销售数字中，最后还要加到年度销售数字中。还要注意整个排序是按照monthfull来排序的，其实是按照MonthNumber 进⾏排序。
总结
在这个例⼦中，我使⽤了Invoices表来测试仅仅因为这⾥存储了很多指标数据，当然你也可以选择其他表来尝试这个智能时间的应⽤。它不仅⽅便了对于数据的分类和⽐较，更提供了⼀种潜在的排序和聚合。
补充：
还可以在数据库中建⽴物理维度表。
创建⽇期维度表：
CREATE TABLE [dbo].[DimDate]([datekey] [int] NOT NULL,[date_name] [date] NOT NULL,[the_Year] [int] NULL,[year_name] [nvarchar](10) NULL,[the_quarter] [int] NULL,[quarter_name] [nvarchar](10) NULL,[the_month] [int] NULL,[month_name] [nvarchar](10) NULL,[the_week] [int] NULL,[week_name] [nvarchar](10) NULL,[the_year_quarter] [int] NULL,
[year_quarter_name] [nvarchar](10) NULL,[the_year_month] [int] NULL,[year_month_name] [nvarchar](10) NULL,
[the_year_week] [int] NULL,[year_week_name] [nvarchar](10) NULL,[the_week_day] [int] NULL,[week_day_name] [nvarchar] (10) NULL,[week_day_type] [nvarchar](10) NULL,CONSTRAINT [PK_DimDate] PRIMARY KEY CLUSTERED([datekey]
ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY])ON [PRIMARY]
也可以使⽤存储过程在物理表中⽣成时间维度表：
USE [DW]
GO
/****** Object: StoredProcedure [dbo].[SP_CREATE_TIME_DIMENSION] Script Date: 2017/12/8 18:34:39 ******/

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。