专栏名称: PowerBI战友联盟

微软 Power BI MVP BI佐罗带你学习BI真经。

财务人收藏：PowerBI 账款账龄分析

PowerBI战友联盟 · 公众号 · · 2024-07-04 11:21

正文

请到「今天看啥」查看全文

各位财务小伙伴在日常工作中最头疼的可能就是做账款账龄分析了，今天就帮大家彻底解决这个难题，使用 Power BI 实现账款账龄分析。

借着一位伙伴提出的问题，我们来看一下如何使用 Power BI 在真实业务场景中实现账款账龄分析。

伙伴的问题：

解答：

先来看一下示例数据，小伙伴的数据是这样的，有两个表。一个是收入发生额表，记录着项目编号、收入时间和具体的收入金额。

另一个表是应收期末余额，记录了每个项目编号的期末余额（欠款）。

这里的期末余额等于收入总额减去收款总金额。

应收期末余额表是由收入总额减去收款总金额得到的，按照真实的业务场景，每个项目编号有多笔收入，收款也会有多笔，而且收款金额不一定等于收入金额。

在审计中，一般是按照先进先出的原则计算账款账龄，这又是什么意思呢？

比如说，某个项目发生了三笔收入，按照时间先后顺序分别是 45000、4000 和 40000，然后某天该项目回款 46000 元，那么这时候按照先进先出原则，就会先还第一笔收入 45000，还完第一笔收入 45000 之后还剩 1000，这 1000 用来偿还 4000 这笔欠款。那这个项目的应收账款就是 3000 + 40000 总计 43000，对应示例数据中的应收期末余额表。但是在计算账龄时要根据发生收入的时间来计算账龄，也就是说 4000 这笔收入的应收账款为 3000，40000 这笔收入的应收账款为 40000。

这跟我们之前介绍的采购销售库存 FIFO 计算模板是一样的。

参考：财务人收藏：PBI 采购销售库存FIFO计算模式

好像有点烧脑，但这就是真实的业务场景。

我们接着往下看，如何在 Power BI 中实现这一计算。

在两个表之间新建一个关系，应收期末余额表跟收入发生额表之间的关系应该为一对多。

建立完关系之后，就可以使用 DAX 实现两表之间的计算了。

我们先来梳理一下计算逻辑，还款是按照先进先出的原则，也就是先发生收入的先还款。所以计算应收账款就要从后往前计算，这时我们就要先划分账龄。

当剩余应收账款小于该账龄时间段对应的收入金额时，说明这是最后一笔欠款。否则账龄内的应收账款就是收入金额，当剩余应收账款小于收入金额时，账龄内的应收账款就是剩余应收账款。

以 0 - 6 个月账龄的应收账款为例，应该先计算 0 - 6 个月的收入发生额，假设今天为做账日期，那么 0 - 6 个月账龄的收入发生额为：

0 - 6 个月收入发生额 =SUMX (    FILTER (        '收入发生额',        DATEDIFF (            [收入时间],            TODAY (),            MONTH        ) <= 6    ),    [收入金额])

所以 0 - 6 个月的应收账款为：

0 - 6 个月应收账款 =IF (    [0 - 6 个月收入发生额] > [期末余额],    [期末余额],    [0 - 6 个月收入发生额])

0 - 6 个月还是比较简单的，接下来看下 7 - 12 个月的应收账款计算。

7 - 12 个月的收入发生额为：

7 - 12 个月收入发生额 =SUMX (    FILTER (        '收入发生额',        DATEDIFF (            [收入时间],            TODAY (),            MONTH        ) <= 12            && DATEDIFF ( [收入时间], TODAY (), MONTH ) > 6    ),    [收入金额])

7 - 12 个月的应收账款与 0 - 6 个月的应收账款计算有所差别，这里的期末余额减去上个账龄的应收账款才是剩余应收账款。


7 - 12 个月应收账款 =IF (    [7 - 12 个月收入发生额] > [期末余额] - [0 - 6 个月应收账款],    [期末余额] - [0 - 6 个月应收账款],    [7 - 12 个月收入发生额])

同理，1 - 2 年的收入发生额为：

1




    
 - 2 年收入发生额 =SUMX (    FILTER (        '收入发生额',        DATEDIFF (            [收入时间],            TODAY (),            MONTH        ) <= 24            && DATEDIFF ( [收入时间], TODAY (), MONTH ) > 12    ),    [收入金额])

所以，1 - 2 年的应收账款为：

1 - 2 年应收账款 =IF (    [1 - 2 年收入发生额] > [期末余额] - [0 - 6 个月应收账款] - [7 - 12 个月应收账款],    [期末余额] - [0 - 6 个月应收账款] - [7 - 12 个月应收账款],    [1 - 2 年收入发生额])

各账龄的应收账款计算依此类推……

结果如下：

再将期末余额列添加到表格中验证一下是否正确。

数据完全匹配：

这里出现应收账款为负值的情况，是因为收入发生额表中有收入金额是负值，应收账款为 0 说明该账龄内发生过收入金额，但是已经还款。

结合中国式矩阵实现如下效果，更加震撼。

中国式矩阵参考：

PBI中国式复杂矩阵 v1.0

PBI中国式复杂矩阵 v2.0

PowerBI 全动态中国式复杂矩阵完美增强版 4.0 版

不仅是应收账款账龄，还得到了各个阶段的收入发生额以及总的应收账款余额的明细数据。如果你的账龄分组不是按照年月分组，而是按照天，直接将 DATEDIFF 中的 MONTH 参数修改为 DAY，然后将月份数修改为对应的天数即可。

这样一个应收账款账龄分析模板，简直是工作中的利器，需要的小伙伴赶快扫描下方二维码找老师领取吧。

如果你对本文的 Power BI 源文件感兴趣可以私信老师了解领取方式。

数据分析精英都会遇到的二十大分析问题

分析师必看：业财人面临的二十大数据分析问题

数据分析精英都在学习的五大能力境界

分析师必备：业务数据分析能力五层成熟度路线图框架全解

↓ 数据分析精英正在学习的课程 ↓

他们是：企业老板，高管，CFO，分析...
可以体验百万级真实企业项目案例，彻底打通任督二脉

一数据分析师训练营课程表一

课程：《业财分析之道》业务财务人数字化能力必修课 🔥 🔥 🔥
2024年7月满
2024年8月少量席位

🎓 更多热门课程推荐👇👇👇

🚀 《业财分析之道》：业务财务人数字化能力必修课。
📈 《经营分析之道》：企业指标拆解及经营分析框架。

🔍 《数据分析之道》：精通分析十大方法加十大模型。
💡 《 PowerBI 真经》：更专业更系统化学习 Power BI 。

🔗 如何获取更多信息？扫描下方二维码具体咨询。

如果您觉得这篇文章对您有帮助或启发

感谢您【点赞】、【在看】

如果您需要添加老师，也请先【点赞】、【在看】

这样老师才能更快地识别并回应

跟BI佐罗老师更专业更系统学企业数据分析

点击“阅读原文”获取更多资源