使用PBI进行应收账款分析

  • Post author:
  • Post category:其他




感谢采悟老师分享的账龄分析的度量值写法

原文链接:

Power BI财务分析应用:应收账款账龄分析 – 知乎 (zhihu.com)



本文在此基础上,再作一些拓展

  1. 使之可以针对不同的过去时间点,分别显示当时的账龄分布情况。
  2. 计算未收到账款的平均日龄、最大日龄


    1


  3. 展示一步步尝试与修改,将看似复杂困难的问题分解为一系列容易实施的多步骤的过程。

[! 示例文件下载:]


百度网盘


提取码:ucwz

应收账款明细数据源由 python 代码虚构。

虚构一份应收账款的明细账,为制作Power BI_芦骁骏的博客-CSDN博客



一、参考采悟老师的分享,建立度量值计算账龄,并扩展其功能



1. 建立度量值,使之能计算期末余额的来源

应收发生额 = SUM('应收账款明细'[借方金额])
应收未收金额.未分客户 = 
var maxDate=CALCULATE(MAX('Date'[日期]),ALLSELECTED('Date'))
var endAmt=CALCULATE(SUM('应收账款明细'[金额]),'Date'[日期]<=maxDate)
var a=[应收发生额]
var b=CALCULATE( [应收发生额],DATESBETWEEN('Date'[日期],MIN('Date'[日期]),maxDate))
var c=CALCULATE( [应收发生额],DATESBETWEEN('Date'[日期],max('Date'[日期])+1,maxDate))
return SWITCH(
	true(),
	b<=endAmt,a,
	b>endAmt,max(0,endAmt-c)
)
应收未收金额.分客户 = 
SUMX(
    VALUES('客户'[客户]),
    [应收未收金额.未分客户]
)

[!注意] 必须要分客户进行计算,否则会因不同客户间的应收款与实收款项的抵消,造成错误

可以未分客户的度量值,嵌套进

SUMX(VALUES('客户'[客户]),...)

解决。

使用以上度量值,可以看到期末各客户未付款的金额,及其这些款账产生的月份。

image.png|500

image.png|600



2. 引用以上度量值,建立计算账龄分组的度量值

应收账款.按账龄分组.错误版 = 
var maxDate=max('Date'[日期])
var lowLimit=MIN('账龄分组2'[下限])
var highLimit=MAX('账龄分组2'[上限])
var beginDate=maxDate-highLimit
var endDate=maxDate-lowLimit
var groupedAmt=CALCULATE(
    [应收未收金额.未分客户],
    DATESBETWEEN('Date'[日期],beginDate,endDate)
)
return groupedAmt
应收账款.按账龄.分客户.错误版 = 
var x=
    SUMX(
        VALUES('客户'[客户]),
        [应收账款.按账龄分组.错误版]
    )
return if(x<>0,round(x,2))

查看结果会发现,存在明显的错误。客户在各账龄分组的金额与总的金额相差甚远。其原因是:

[应收账款. 按账龄分组. 错误版]

度量值中引用到了

[应收未收金额.未分客户]

。而

[应收未收金额.未分客户]

中有

var maxDate=CALCULATE(MAX('Date'[日期]),ALLSELECTED('Date'))

定义的

maxDate


maxDate

在上面的表中计算

期末余额来源

时能正确计算出

2022-12-31


maxDate



[应收账款.按账龄分组.错误版]

的计算中,由于外层套着

CALCULATE(....,DATESBETWEEN(...))

,其会影响到

maxDate

的计算环境,使其计算结果为

DATESBETWEEN(...)

中的最大值,

endDate

image.png|600



3. 修正上述错误

因为

ALLSELECTED

函数的一些固有限制,嵌套在最里的它,在这里是无法正确取到我们想要那一层筛选上下文中的最大日期的。为了解决上面的错误,我们选择将

[应收未收金额.未分客户]

的代码直接写进

[应收账款.按账龄分组]

内,并将

maxDate

提到

CALCULATE(....,DATESBETWEEN(...))

之外。

修改后的代码如下:

应收账款.按账龄分组.修正 = 
var maxDate=max('Date'[日期])
var lowLimit=MIN('账龄分组2'[下限])
var highLimit=MAX('账龄分组2'[上限])
var beginDate=maxDate-highLimit
var endDate=maxDate-lowLimit
var groupedAmt=CALCULATE(
    var endAmt=CALCULATE(SUM('应收账款明细'[金额]),'Date'[日期]<=maxDate)
    var a=[应收发生额]
    var b=CALCULATE( [应收发生额],DATESBETWEEN('Date'[日期],MIN('Date'[日期]),maxDate))
    var c=CALCULATE( [应收发生额],DATESBETWEEN('Date'[日期],max('Date'[日期])+1,maxDate))
    return SWITCH(
        true(),
        b<=endAmt,a,
        b>endAmt,max(0,endAmt-c)
    ),
    DATESBETWEEN('Date'[日期],beginDate,endDate)
)
return groupedAmt
应收账款.按账龄.分客户.修正 = 
var x=
    SUMX(
        VALUES('客户'[客户]),
        [应收账款.按账龄分组.修正]
    )
return if(x<>0,round(x,2))

使用以上代码已能基础正确计算账龄分组,但还是有个小错误。如下图第一行,

总计

的金额与

1-2 个月

的金额相等,而分组中

1 个月以内

也还有金额。而下面的行则没有这个错误。

其原因是正好有一笔应收款,既分类到

1 个月以内

又分类到

1-2 个月

了。

image.png|500

var maxDate=max('Date'[日期])
var lowLimit=MIN('账龄分组2'[下限])
var highLimit=MAX('账龄分组2'[上限])
var beginDate=maxDate-highLimit
var endDate=maxDate-lowLimit

看这段代码,如果

lowLimit



highLimit

相差 30 。比如说是 0 和 30 。则

beginDate



endDate

也会相差 30 。

DATESBETWEEN

函数会将两端都保留,一共会是 31 个日期。这与我们设计的分组规则不符。显然,最适一天,也就是数值 0,我们是要保留的。所以,只要将

beginDate

加 1 。将其代码改为

var beginDate=maxDate-highLimit

已得正确结果:

image.png|500



4. 将客户字段换成日期表中的字段, 也能正确计算。

比如换成年或年月、日期,也能正确计算在以前的时间结点上的账龄分布情况。借此,我们可以做图或表查看变化趋。发现一些异常的变化。

image.png



二、计算平均日龄、最大日龄



1.针对一个客户一个日期点,在度量值中先建立一张表,包括每笔未收到的应收账款的产生日期和金额

由于在 PBI 的度量值结果不能是一个表格,我们选择先在

DAX Studio

中进行尝试。选一个在某日(比如 2021-12-31)有数据的客户,建立一张上述的表格。

EVALUATE
CALCULATETABLE(
	ADDCOLUMNS(
		VALUES('Date'[日期]),
		"应收未收金额",
		[应收未收金额.未分客户]
	),
	'Date'[日期]=date(2021,12,31),
	'客户'[客户]="Arias-Erickson"
)

选择”Arias-Erickson”在 2021-12-31,简单地建一个表格,尝试一下。

iShot_2023-01-20_23.10.15.png

结果不对,原因是因为

VALUES('Date'[日期])



'Date'[日期]=date(2021,12,31)

影响,只有一个值。在这里只能使用

ALL

并加上筛选,筛选早于

date()

中指定的值。

EVALUATE
CALCULATETABLE(
	ADDCOLUMNS(
		filter(
			ALL('Date'[日期]),
			'Date'[日期]<=date(2021,12,31)
		),
		"应收未收金额",
		[应收未收金额.未分客户]
	),
	'Date'[日期]=date(2021,12,31),
	'客户'[客户]="Arias-Erickson"
)

iShot_2023-01-20_23.18.49.png

结果中空行太多,加一步筛选:

EVALUATE
CALCULATETABLE(
	FILTER(
		ADDCOLUMNS(
			filter(
				ALL('Date'[日期]),
				'Date'[日期]<=date(2021,12,31)
			),
			"应收未收金额",
			[应收未收金额.未分客户]
		),
		[应收未收金额]<>0  
	),
	'Date'[日期]=date(2021,12,31),
	'客户'[客户]="Arias-Erickson"
)

得到结果:

image.png

与视觉对象中显示的相同。(这里查看是在 21 年末的客户账龄,注意筛选条件中不要选到 2022 年)

image.png



2. 利用该表计算出一个客户在一个日期的平均日龄

调试后的代码如下:

EVALUATE
CALCULATETABLE(
	var maxDate=max('Date'[日期])
	var restTable=
		FILTER(
			ADDCOLUMNS(
				filter(
					ALL('Date'[日期]),
					'Date'[日期]<=date(2021,12,31)
				),
				"应收未收金额",
				[应收未收金额.未分客户]
			),
			[应收未收金额]<>0  
		)
	var tableAddDays=
		ADDCOLUMNS(
			restTable,
			"日龄",
			VALUE(maxDate-'Date'[日期])
		)
	var avgDays=
		DIVIDE(
			SUMX(
				tableAddDays,
				[应收未收金额]*[日龄]
			),
			SUMX(
				tableAddDays,
				[应收未收金额]
			)
		)
	return
		{avgDays},
	'Date'[日期]=date(2021,12,31),
	'客户'[客户]="Arias-Erickson"
)

在调试过程中,如果遇到错误,可以通过修改

return

后的内容,将相应的中应过程输出进行查看。如果输出的不是表而是值,需要使用花括号包裹。因为

DAX Studio



Evaluate

是用于计算表格的。比如:

tableAddDays

的具体数据:

image.png

以上代码:可以已经正确输出计算结果。

image.png



3. 尝试改进上述代码,使其能够适应不同客户在不同日期的平均日龄计算

最终的度量值需要放到如下图所示的矩阵中进行计算。

image.png

我们可以继续在

DAX Studio

进行调试:



calculate( ...,'Date'[日期]=date(2021,12,31),'客户'[客户]="Arias-Erickson")

这部分分离出去,使用

crossjoin(客户,日期)

构建完整的客户与日期的组合。

将另外部分使用

define

定义为一个

DAX Studio

中的度量值。

初步将代码修改如下:

DEFINE Measure
'00DAX'[myMeasure]=
	var maxDate=max('Date'[日期])
	var restTable=
		FILTER(
			ADDCOLUMNS(
				filter(
					ALL('Date'[日期]),
					'Date'[日期]<=maxDate //替换掉原先的date(2021,12,31)
				),
				"应收未收金额",
				[应收未收金额.未分客户]
			),
			[应收未收金额]<>0  
		)
	var tableAddDays=
		ADDCOLUMNS(
			restTable,
			"日龄",
			VALUE(maxDate-'Date'[日期])
		)
	var avgDays=
		DIVIDE(
			SUMX(
				tableAddDays,
				[应收未收金额]*[日龄]
			),
			SUMX(
				tableAddDays,
				[应收未收金额]
			)
		)
	return
		avgDays
		
EVALUATE
var daysTable=
	ADDCOLUMNS(
		CROSSJOIN(
			VALUES('Date'[年序号]),
			VALUES('客户'[客户])
		),
		"myMeasure",
		[myMeasure]
	)
var daysTableFilter=
	FILTER(
		daysTable,
		[myMeasure]<>0
	)
return daysTableFilter

image.png

显然,计算结果还不正确。这里的错误原因,与上文

一、2.

中的错误原因一样。因为在计算

restTable

时用了度量值

[应收未收金额.未分客户]

该度量值中有一段

var maxDate=CALCULATE (MAX ('Date'[日期]), ALLSELECTED ('Date'))

。修改的方法,是要让它直接使用外面定好的

maxDate



image.png



4. 修正错误

将上图中的

[应收未收金额.未分客户]

替换为如下代码:

var endAmt=CALCULATE(SUM('应收账款明细'[金额]),'Date'[日期]<=maxDate)
var a=[应收发生额]
var b=CALCULATE( [应收发生额],DATESBETWEEN('Date'[日期],'Date'[日期],maxDate))
var c=CALCULATE( [应收发生额],DATESBETWEEN('Date'[日期],'Date'[日期]+1,maxDate))
return
IF(
	'Date'[日期]<=maxDate,
	SWITCH(
		true(),
		b<=endAmt,a,
		b>endAmt,max(0,endAmt-c)
	)
)

这段代码中原先有用

var

定义

maxDate

。但这里要用外层定义好的

maxDate

,所以直接省略掉就好。这样已经可以得到正确的计算结果。

image.png



5. 将上文中

define

内定义的度量值写到 PBI 中

得到最终的度量值:

平均日龄 = 
var maxDate=max('Date'[日期])
var restTable=
    FILTER(
        ADDCOLUMNS(
            filter(
                ALL('Date'[日期]),
                'Date'[日期]<=maxDate
            ),
            "应收未收金额",
            var endAmt=CALCULATE(SUM('应收账款明细'[金额]),'Date'[日期]<=maxDate)
            var a=[应收发生额]
            var b=CALCULATE( [应收发生额],DATESBETWEEN('Date'[日期],'Date'[日期],maxDate))
            var c=CALCULATE( [应收发生额],DATESBETWEEN('Date'[日期],'Date'[日期]+1,maxDate))
            return
            IF(
                'Date'[日期]<=maxDate,
                SWITCH(
                    true(),
                    b<=endAmt,a,
                    b>endAmt,max(0,endAmt-c)
                )
            )
        ),
        [应收未收金额]<>0  
    )
var tableAddDays=
    ADDCOLUMNS(
        restTable,
        "日龄",
        VALUE(maxDate-'Date'[日期])
    )
var avgDays=
    DIVIDE(
        SUMX(
            tableAddDays,
            [应收未收金额]*[日龄]
        ),
        SUMX(
            tableAddDays,
            [应收未收金额]
        )
    )
return
    avgDays

[!note]


return

后的结果,如果在

DAX Studio

中有花括号包裹,在 PBI 里则要去掉。度量值在

DAX Studio

中返回表,度量值要加花括号变成一个值的表;在 PBI 中返回值,要将花括号去掉。

即可使用矩阵查看各客户在不同日期的应收账款平均日龄。如果放入的字段是月份、年份之类的期间,则计算的该期间期末的数据。

image.png



6. 将上述代码稍作修改,即可得到度量值

[最大日龄]

具体内容见下载附件。


  1. 指没客户没付款的各笔款项距离统计日的天数, 统计日是列标题所指月份的最后一天

    ↩︎



版权声明:本文为weixin_38775843原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。