财年就是财经年度,一般国内公司的话,大概应该是从一月一日到十二月三十一日,正常时间周期就是一个财年,外企的话,有的公司有点不一样。有的公司是从每年的10月开始算下一个财年。比如,2019年10月,就算为2020年1月。也就是,我们讨论2020年第一财月的时候说的是2019年10月。和日历年有点区别。不过,都是不同的代号而已。只要保证大家讨论的口径一致,数据上来说,也就应该能对的上。
这里以财年周期为10月到次年9月为周期的举个例子,写个DAX的表达式。
step1: 先建一个简单的数据表。financials是我的一个事实表,这里Date Table表是根据事实表中的Date数据来调节的。这些都不重要,最主要的是
Date table =
VAR MinYear = YEAR ( MIN ( financials[Date]) )
VAR MaxYear = YEAR ( MAX ( financials[Date]) )
RETURN
ADDCOLUMNS (
FILTER (
CALENDARAUTO( ),
AND ( YEAR ( [Date]) >= MinYear, YEAR ( [Date] ) <= MaxYear )
),
"Month Name", FORMAT ( [Date], "mmmm" ),
"Month Number", MONTH ( [Date] ),
"Year",YEAR([Date])
)
这些都不重要,最主要的是我们新建的Date Table里面包含时间Date,Month Name还有Month Number,Year这些内容。数据内容如下:
step2:根据逻辑写出Fiscal Month的表达式
Fiscal Month = IF('Date table'[Month Number] >= 10, 'Date table'[Month Number]-9,'Date table'[Month Number]+3)
得到结果如下:
有朋友有特殊要求,要显示成“001.2013”这样的。所以加入特殊的Fiscal Year Month如下:
Fin Year Month = VAR yr = IF('Date table'[Month Number]>=10,'Date table'[Year] +1,'Date table'[Year])VAR mt = IF('Date table'[Fiscal Month]<=9,"0"&'Date table'[Fiscal Month],'Date table'[Fiscal Month])VAr rt = "0"&mt&"."&yr Return rt
得到结果如下:
还可以吧。主要是数字规律,计算逻辑搞清楚就好了。
然后说下关账时间。
自然月份来说就是从当月1号到最后一天,但是有时候不同公司也有不同的关账时间。也就是可能算一个月的数据的时候,不是自然月的一个月,可能有的月的日期算下个月的数据。比如,有的公司是按照445weeks来关账的。即第一个月是按照4周来算,第二个月也是4周,然后第三个月是5周的时间,第四个月是4周时间,以此类推。。。所以每个月的关账时间算得累啊~~
按照445周算出来的全年的不同财月如下:
Finance_Month = IF('Date'[Week Number] <=4,'Date'[Year] & "-1", IF(AND('Date'[Week Number]>4,'Date'[Week Number]<=8),'Date'[Year] & "-2", IF(AND('Date'[Week Number]>8,'Date'[Week Number]<=13),'Date'[Year] & "-3", IF(AND('Date'[Week Number]>13,'Date'[Week Number]<=17),'Date'[Year] & "-4", IF(AND('Date'[Week Number]>17,'Date'[Week Number]<=21),'Date'[Year] & "-5", IF(AND('Date'[Week Number]>21,'Date'[Week Number]<=26),'Date'[Year] & "-6", IF(AND('Date'[Week Number]>26,'Date'[Week Number]<=30),'Date'[Year] & "-7", IF(AND('Date'[Week Number]>30,'Date'[Week Number]<=34),'Date'[Year] & "-8", IF(AND('Date'[Week Number]>34,'Date'[Week Number]<=39),'Date'[Year] & "-9", IF(AND('Date'[Week Number]>39,'Date'[Week Number]<=43),'Date'[Year] & "-10", IF(AND('Date'[Week Number]>43,'Date'[Week Number]<=47),'Date'[Year] & "-11", IF(AND('Date'[Week Number]>47,'Date'[Week Number]<=53),'Date'[Year] & "-12","wrong"))))))))))))
根据财月来算出每个月的关账日期:
Closed_Date = VAR max_dt = 'Date'[Finance_Month] return CALCULATE(MAX('Date'[Date]),FILTER('Date','Date'[Finance_Month] = max_dt))
得到的结果如下图:
Date和Year Month是自然时间,Finance_Month和Closed_Date是计算得到的结果。
好像也不难的。
附带关账日期例子的日期表表达式如下:
Date = GENERATE ( CALENDAR("2018-01-01","2099-12-31"), VAR YYYY = YEAR ( [Date] ) VAR MMMM = MONTH ( [Date] ) RETURN ROW ( "Year", YYYY , "Month", FORMAT ( [Date], "mmmm" ), "Month Number", MMMM, "Week Number", WEEKNUM([Date],2), "WeekDay", FORMAT ( [Date], "dddd" ), "Year Month", IF(MMMM < 10,YYYY &"-0"&MMMM,YYYY &"-"&MMMM) ))
就到这里了。
谢谢关注。