今天遇到一个好玩的需求。不过快放假了,时间仓促,就下次分享吧。
先说一个概念:Calendar table。为什么要说这个呢,因为你要用DAX里面的Time intelligence的函数的一个前提,就是Date table要是一个Calendar table。否则,函数怎么写也不work...敲黑板,怎么是一个Calendar table呢?建立关系的时候,用date和date关联,不要用FK和PK。记住了吗?这个非常非常重要!用date和date关系的date table就是Calendar table了。
然后我们开始在PowerBI中建Date Table了。
第一种方法M Query创建Date table
这个M Query,真的是又爱又恨。玩转数据真的很方便,不过语法真的不熟悉啊,之前对DAX不熟悉的时候,我都在M Query这边倒腾。后来DAX还可以之后,就不怎么待见M Query了。这方面的资料不多,或者其实我应该多分享点这个。
点开Home->Edit Queries,选择Edit Queries。然后看到如下页面:
点在左边黑色的地方,点击New Query->Blank Query.然后在写计算的地方贴上以下内容。具体的起始和结束时间,你可以再下面的StartDate和EndDate里面定义。随便你写多久啦。
代码如下:
= (StartDate as date, EndDate as date, optional Culture as nullable text) as table =>
let
DayCount = Duration.Days(Duration.From(EndDate - StartDate)),
Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),
ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),
RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),
InsertYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date])),
InsertQuarter = Table.AddColumn(InsertYear, "QuarterOfYear", each Date.QuarterOfYear([Date])),
InsertMonth = Table.AddColumn(InsertQuarter, "MonthOfYear", each Date.Month([Date])),
InsertDay = Table.AddColumn(InsertMonth, "DayOfMonth", each Date.Day([Date])),
InsertDayInt = Table.AddColumn(InsertDay, "DateInt", each [Year] * 10000 + [MonthOfYear] * 100 + [DayOfMonth]),
InsertMonthName = Table.AddColumn(InsertDayInt, "MonthName", each Date.ToText([Date], "MMMM", Culture), type text),
InsertCalendarMonth = Table.AddColumn(InsertMonthName, "MonthInCalendar", each (try(Text.Range([MonthName],0,3)) otherwise [MonthName]) & " " & Number.ToText([Year])),
InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "QuarterInCalendar", each "Q" & Number.ToText([QuarterOfYear]) & " " & Number.ToText([Year])),
InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "DayInWeek", each Date.DayOfWeek([Date])),
InsertDayName = Table.AddColumn(InsertDayWeek, "DayOfWeekName", each Date.ToText([Date], "dddd", Culture), type text),
InsertWeekEnding = Table.AddColumn(InsertDayName, "WeekEnding", each Date.EndOfWeek([Date]), type date)
in
InsertWeekEnding
选好时间之后,点击‘invoke’就会生成一个Invoked Function。也就是你的M Query建立的Date table了。之后就和从外面导入的数据源没啥差别了,随便用。别客气。
基本上够用了,如果你还想加别的时间的内容。关注DAX Share,私信我,帮你加哦。
第二种方法就是DAX创建Date table了。方法很多,好纠结啊。
点开Modeling->New Table。
主要的函数就是CALENDARAUTO()。
随便两段贴一下吧。我主要用年和月,所以就不加季度了,需要的自己加加看,不会的关注我,私信。
Date =
VAR MinYear = YEAR ( MIN ( financials[Date]) )
VAR MaxYear = YEAR ( MAX ( financials[Date]) )
RETURN
ADDCOLUMNS (
FILTER (
CALENDARAUTO( ),
AND ( YEAR ( [Date]) >= MinYear, YEAR ( [Date] ) <= MaxYear )
),
"Calendar Year", "CY " & YEAR ( [Date] ),
"Month Name", FORMAT ( [Date], "mmmm" ),
"Month Number", MONTH ( [Date] )
)
或者
Date 2 =
GENERATE (
CALENDARAUTO (),
VAR YYYY =
YEAR ( [Date] )
VAR MMMM =
MONTH ( [Date] )
RETURN
ROW (
"Year", CONCATENATE ( "CY ", YYYY ),
"Month", FORMAT ( [Date], "mmmm" ),
"Month Number", MMMM
)
)
er,如果代码太多记不住怎么办,很简单的。就New Table,然后在写DAX的地方写一句table = CALENDARAUTO ()。然后自己慢慢加入新字段吧。DAX Share之后会推出基础课程,到时候就会告诉你怎么简单的加入你要的月份,季度什么的。so easy!
至于Date表来自Dim Date,我就不多说了。你都懂Dim Date了,我就不废话了。请记住不要用PK和FK关联就好啦!感觉自己好啰嗦啊~~
最后,创建Date Table就是这么简单啦。敲重点!
Date table和数据事实表关联一定要用Date和Date关联,要不然Time intelligence的函数都不起作用。(什么?不知道time intelligence函数有哪些?就是我之前写过的那些计算YTD,Rolling 12 month那些用到的和日期分析有关的函数啦!)
DAX创建Date table,啥也没记住,就记住CALENDARAUTO ()!够用啦,慢慢来,easy easy~~
清明节回来分享下一期啦!
欢迎关注DAX Share。
。