我试图根据我创建的日历表计算特定年份的天数 .
例如:我有3列 .
事件,天数和此事件开始的日期
Event DaysLost
Injury 30 25/12/2016
Injury 588 06/08/2012
2016年的日子 - 6
2017年的天数 - 24
对于第二种情况:
2012年的天数 - 146
2013年的天数 - 365
2014年的天数 - 77
现在对于上述情况,2016年只需要计算6天,其余时间应自动计入2017年 . 但我无法弄清楚如何做到这一点 .
在我的输出中,我想在一个专栏中放置多年,并且在该特定年份前的一年中失去了一天 .
我有一个日历表,我想要在特定年份填充一天 .
我尝试通过获取结束日期来计算它,通过向第一个开始日期添加天数,然后如果天数超过该年剩余的天数 . 从总天数减去剩余天数,剩余天数应减少到明年 . 但是,如果天数延长多年并且在单词之后列出,我无法弄清楚如何继续为未来几年增加天数 .
2017年9月4日请参阅下面的excel解决方案
2 回答
0) 将Excel屏幕截图中的数据导入Power BI会导致此问题 .
1) 使用以下公式为该结束日期创建该表中的新列(以帮助处理将来的公式) .
您说您有一个日历表,因此您可以跳到第3步
2) 单击建模 - >新表并输入以下公式创建新表 . 这为单个列表提供了年份列表 .
3) 使用以下公式创建另一个新表 . 这给出了一个表,其中包含初始数据表中与刚刚创建的Year表交叉连接的所有字段 . 该公式还会将结果表过滤为仅返回“年”列中的值介于“第一个开始日期”和“第一个开始日期”加上“天”之间的行 . 要了解有关
CROSSJOIN
函数的更多信息,请查看文档here .4) 从InjuriesByYear表创建关系到初始数据表和Year表 . 这将有助于促进更好的报告工作 .
5) 在InjuriesByYear表中,单击Modeling - > New Column并输入以下公式,创建一个新列 . 第一个
IF
检查所有丢失的天数是否在一年内 . 第二个IF
处理日期分布在多年的时间,True子句处理第一年,False子句处理所有其他年份 .6) 要全部测试,请按照下面的配置创建数据透视表 . 执行这些步骤后,数据透视表应与您的Excel解决方案匹配 .
This is a Power Query based approach...
我从这开始:
然后,我通过单击“添加列”选项卡和“自定义列”按钮添加自定义列,并完成弹出窗口,如下所示:
...然后单击“确定” .
然后我通过选择它然后单击Transform选项卡然后单击Data Type和Date来更改该新列的类型 .
然后我添加了另一个自定义列,完成弹出窗口,如下所示:
然后我添加了另一个自定义列,完成弹出窗口,如下所示:
然后我添加了另一个自定义列,完成弹出窗口,如下所示:
然后我通过单击列顶部的
并展开到新行来扩展我添加的最后一列 .
然后我添加了一个最终的自定义列,完成弹出窗口,如下所示:
最后,我按Event,DaysLost,Started和Year列进行分组,并通过单击Transform选项卡和Group By按钮并完成弹出窗口来总结DaysLostForYear列:
我最终得到了这个:
你可能想要一个不同的分组,但这应该让你接近 . 它显示了与每次伤害总天数相关的年份中丢失了多少天 . 例如,第一次伤害持续30天,于2016年12月25日开始:其中7天发生在2016年,23起发生在2017年 . 第二次伤害是588天,从2012年8月6日开始:148 2012年为365天,2013年为365天,2014年为75天 .
请注意,我将开始日期视为丢失的一天 . 还要注意我占闰年 .
我希望这有帮助 .
这是查询代码: