加载中…
个人资料
PowerPivot工坊
PowerPivot工坊
  • 博客等级:
  • 博客积分:0
  • 博客访问:4,509
  • 关注人气:79
  • 获赠金笔:0支
  • 赠出金笔:0支
  • 荣誉徽章:
正文 字体大小:

在Power Pivot中用DATEDIFF函数计算两个日期之间的间隔

(2017-09-28 15:30:59)
标签:

it

教育

时评

分类: 数据分析

​经常有朋友问在Power Pivot中是否有类似Excel中的DATEDIF函数,用来计算2个日期之间的间隔。如果你用的是Excel 2016 或Office 365,或Power BI Desktop, 那么DAX中提供了新的DATEDIFF函数可以实现。如果你用的是Excel 2013,还没有这个DAX函数,就需要用其它组合函数来实现。

我们先来看一下Excel中DATEDIF的用法:

https://wx3/large/006vR4QNly1fjz3ofoiyyj30f606b3zy.jpgPivot中用DATEDIFF函数计算两个日期之间的间隔" TITLE="在Power Pivot中用DATEDIFF函数计算两个日期之间的间隔" />

​通过函数=DATEDIF(A2,B2,"Y")得到两个日期之间的年的间隔。如果把第三个参数"Y"换成"M"或"D",则分别对应月和日。

将数据导入到Power Pivot中,输入DATEDIFF(注意,是DATEDIFF, Excel中是DATEDIF,差了一个F),DATEDIFF 需要3个参数,日期1,日期2,和间隔单位。

https://wx1/large/006vR4QNly1fjz3p7h9ldj30ga06cac5.jpgPivot中用DATEDIFF函数计算两个日期之间的间隔" TITLE="在Power Pivot中用DATEDIFF函数计算两个日期之间的间隔" />

​=DATEDIFF('测试'[开始日期],'测试'[结束日期],YEAR)

最终计算结果如下:

https://wx1/large/006vR4QNly1fjz3pwpaw9j30fw03zta7.jpgPivot中用DATEDIFF函数计算两个日期之间的间隔" TITLE="在Power Pivot中用DATEDIFF函数计算两个日期之间的间隔" />

​同理,如果我们把DATEDIFF的第三个参数改成Month和 Day,则可以获取2个日期之间间隔的月份数和天数。还可以得到季度、小时、分钟、秒数等。

https://wx2/large/006vR4QNly1fjz3qi5tsqj30hs03fq45.jpgPivot中用DATEDIFF函数计算两个日期之间的间隔" TITLE="在Power Pivot中用DATEDIFF函数计算两个日期之间的间隔" />

​使用DATEDIFF可以方便地计算日期时间间隔,但有一点需要注意,大家看下图中红圈的内容。Excel里的DATEDIF和DAX里的DATEDIFF计算结果不一致:

https://wx3/large/006vR4QNly1fjz3qs5g0lj30cc02zgmc.jpgPivot中用DATEDIFF函数计算两个日期之间的间隔" TITLE="在Power Pivot中用DATEDIFF函数计算两个日期之间的间隔" />

​当开始日期是6/30/2016,结束日期是6/29/2017时,Excel中DATEDIF(A2,B2,"Y") 公式的计算结果严格按照日期计算,没有到一年就算0,而DAX中DATEDIFF('测试'[开始日期],'测试'[结束日期],YEAR) 的计算则是按照两个日期所处的年份来计算,2017-2016=1年 。

所以如果你在Power Pivot中不想要这样的计算方法,比如计算年龄的时候,想实现Excel中DATEDIF的计算方法,则可以使用下面的公式:

=YEARFRAC('测试'[开始日期],'测试'[结束日期])

https://wx2/large/006vR4QNly1fjz3reogcbj30fr042401.jpgPivot中用DATEDIFF函数计算两个日期之间的间隔" TITLE="在Power Pivot中用DATEDIFF函数计算两个日期之间的间隔" />

​然后通过ROUNDDOWN向下取整实现Excel中DATEDIF整年的计算:

=ROUNDDOWN(YEARFRAC('测试'[开始日期],'测试'[结束日期]),0)

https://wx4/large/006vR4QNly1fjz3rzat2dj30h303w0ua.jpgPivot中用DATEDIFF函数计算两个日期之间的间隔" TITLE="在Power Pivot中用DATEDIFF函数计算两个日期之间的间隔" />

​同理,如果我们用向上取整函数ROUNDUP则可以实现和DAX中DATEDIFF一样的计算结果。而这个函数组合也可以在Excel2013中使用,也就是没有DATEDIFF的情况下实现间隔年的计算。


【间隔月份的计算】

间隔月份也是工作中比较常见的计算,下面我们同样拿Excel中的公式:

=DATEDIF(A2,B2,"M")

 和Power Pivot中的公式:

=DATEDIFF('测试'[开始日期],'测试'[结束日期],MONTH)

来进行对比

https://wx3/large/006vR4QNly1fjz3tjj1esj30fj03zabg.jpgPivot中用DATEDIFF函数计算两个日期之间的间隔" TITLE="在Power Pivot中用DATEDIFF函数计算两个日期之间的间隔" />

​我们看到两个计算结果依然存在差异,Excel中的DATEDIF当日期没有满一个月的时候不计算,而Power Pivot中计算(参考上图第一行数据)。

我们尝试用其它函数组合实现该计算(Excel2013的Power Pivot也可用):

=(YEAR('测试'[结束日期])-YEAR('测试'[开始日期]))*12+MONTH('测试'[结束日期])-MONTH('测试'[开始日期])

该计算的意思是先用两个日期间隔的年份数乘以12个月,然后加上[结束日期]中的月份数减去[开始日期]中的月份数,结果和DATEDIFF-DAX一样,忽略日期,直接用月份相减。

https://wx2/large/006vR4QNly1fjz3ulku6bj30hs03075c.jpgPivot中用DATEDIFF函数计算两个日期之间的间隔" TITLE="在Power Pivot中用DATEDIFF函数计算两个日期之间的间隔" />

​我们还是关注第一行数据,如果开始日期是6/30/2016,结束日期是6/29/2017,还差1天才满12个月,如果我们希望只计算间隔的整月,也就是计算结果为11,可以对公式进一步调整:

=IF(DAY('测试'[结束日期])>=DAY('测试'[开始日期]),0,-1)+(YEAR('测试'[结束日期])-YEAR('测试'[开始日期]))*12+MONTH('测试'[结束日期])-MONTH('测试'[开始日期])

通过判断2个日期之间日的区别,来决定是否计算:如果结束日期的日大于等于开始日期的日,则直接计算即可,否则在计算结果上-1,实现间隔整月的计算。

https://wx4/large/006vR4QNly1fjz3vlfyduj30hs01y3z4.jpgPivot中用DATEDIFF函数计算两个日期之间的间隔" TITLE="在Power Pivot中用DATEDIFF函数计算两个日期之间的间隔" />

​这个计算方法,不管是在Excel2013还是2016的Power Pivot中都可以使用。


【间隔日,小时、分钟、秒的计算】

剩下的间隔日、小时、分、秒就比较简单了。可以直接用DATEDIFF来实现

间隔日=DATEDIFF('测试'[开始日期],'测试'[结束日期],DAY)

间隔小时=DATEDIFF('测试'[开始日期],'测试'[结束日期],HOUR)

间隔分钟=DATEDIFF('测试'[开始日期],'测试'[结束日期],MINUTE)

间隔秒=DATEDIFF('测试'[开始日期],'测试'[结束日期],SECOND)


在Excel2013 Power Pivot中也可以直接用日期相减:

间隔日='测试'[结束日期]-'测试'[开始日期]

间隔小时=24*('测试'[结束日期]-'测试'[开始日期])

间隔分钟=24*60*('测试'[结束日期]-'测试'[开始日期])

间隔秒=24*60*60*('测试'[结束日期]-'测试'[开始日期])

这就是我们今天的分享内容,更多Power Pivot DAX函数的用法请参考我们的视频课程-Excel Power Pivot数据建模分析(基础篇)。

0

阅读 收藏 喜欢 打印举报/Report
  

新浪BLOG意见反馈留言板 欢迎批评指正

新浪简介 | About Sina | 广告服务 | 联系我们 | 招聘信息 | 网站律师 | SINA English | 产品答疑

新浪公司 版权所有