在商业数据分析中,识别连续满足特定条件的时间段是一项常见需求。本篇文章将详细介绍如何在PowerBI中找出连续销售额大于某个阈值的日期范围,这一技巧对于销售趋势分析、异常检测和业务决策都具有重要价值。
问题描述
为了分析客户购买行为,现在需要找出连续销售额大于100的各个日期范围,请自行建模完成计算。具体问题如下图所示:
本案例初始数据只有单个表,其完整数据如下:
销售表:
YearMonth | Date | Sales |
---|---|---|
202501 | 2025/01/24 | 84 |
202501 | 2025/01/26 | 120 |
202501 | 2025/01/28 | 130 |
202501 | 2025/01/30 | 115 |
202501 | 2025/01/31 | 68 |
202502 | 2025/02/01 | 150 |
202502 | 2025/02/03 | 90 |
202502 | 2025/02/04 | 140 |
202502 | 2025/02/08 | 120 |
202502 | 2025/02/09 | 130 |
202502 | 2025/02/10 | 80 |
202502 | 2025/02/11 | 105 |
202502 | 2025/02/12 | 110 |
若需其它辅助表等,可自行创建并建模。
解题要点
1、可以先找出满足条件的日期,然后再进行分组取最小日期和最大日期并拼接文本返回。
2、由于日期并不是严格连续,因此比较简单的方式就是使用窗口函数,比如使用OFFSET函数来获取上一个日期与下一个日期所对应的销售额,只要其中任意一个超出阈值,并且当前日期的销售额也是超出阈值的,那么当前日期就是连续日期中的一个。
3、最后进行分组并拼接文本返回即可,分组的方法或逻辑可参考下图:
解决方案
首先,数据模型如下图所示:
然后,创建如下度量值:
连续销售大于100的日期范围 =
VAR N = 100
VAR AddTag =
ADDCOLUMNS(
'销售表',
"IsSatisfied",
VAR CurVal = '销售表'[Sales]
VAR PreVal = MAXX(OFFSET(-1,DISTINCT('销售表'),ORDERBY('销售表'[Date])),'销售表'[Sales])
VAR NextVal = MAXX(OFFSET(1,DISTINCT('销售表'),ORDERBY('销售表'[Date])),'销售表'[Sales])
RETURN
IF(CurVal>N && (PreVal>N || NextVal>N),0,1)
)
VAR Grp = ADDCOLUMNS(AddTag,"Grp",SUMX(FILTER(AddTag,'销售表'[Date]<=EARLIER('销售表'[Date])),[IsSatisfied]))
RETURN
CONCATENATEX(
FILTER(
SUMMARIZE(Grp,[IsSatisfied],[Grp],"Period",FORMAT(MIN('销售表'[Date]),"yyyy/mm/dd")&" - "&FORMAT(MAX('销售表'[Date]),"yyyy/mm/dd")),
[IsSatisfied]=0
),
[Period],
UNICHAR(10)
)
最后创建一个矩阵,并将月份字段作为行标签,再将上面的度量值放入值字段即可,如下图所示:
虽然上面这个度量值的结果并没有问题,但其中的AddTag
变量的步骤其实是多余的。因为本案例要计算的是时间段的起始日期与结束日期的文本拼接,因此其实并不需要判断每个日期是否属于连续超阈值日期,可以直接进行分组的。
改进后的度量值如下:
连续销售大于100的日期范围 =
VAR N = 100
VAR TempTbl = ADDCOLUMNS(VALUES('销售表'[Date]),"Sales",CALCULATE(SUM('销售表'[Sales])))
VAR Grp = ADDCOLUMNS(TempTbl,"Grp",IF([Sales]>N,COUNTROWS(FILTER(TempTbl,[Sales]<=N && '销售表'[Date]<EARLIER('销售表'[Date])))))
RETURN
CONCATENATEX(
FILTER(
SUMMARIZE(
Grp,[Grp],
"Period",FORMAT(MIN('销售表'[Date]),"yyyy/mm/dd")&" - "&FORMAT(MAX('销售表'[Date]),"yyyy/mm/dd"),
"Num",COUNT('销售表'[Date])
),
[Num]>1 && [Grp]<>BLANK()
),
[Period],
UNICHAR(10)
)
结果如下图:
此外,为方便用户查看,也可以使用折线图等视觉对象进行可视化,如下图所示:
折线图中使用到的度量值如下:
连续超阈值范围 =
VAR N = 100
VAR CurDate = MAX('销售表'[Date])
VAR CurVal = SUM('销售表'[Sales])
VAR PreVal = CALCULATE(SUM('销售表'[Date]),OFFSET(-1,ORDERBY('销售表'[Date])))
VAR NextVal = CALCULATE(SUM('销售表'[Date]),OFFSET(1,ORDERBY('销售表'[Date])))
RETURN
IF(CurVal>N && (PreVal>N || NextVal>N),CurVal)
总结
以上方法仅供参考,若有更优雅的解决方案,欢迎留言讨论~