Power BI动态日期轴方法总结

趋势,应该是我们做可视化时最熟悉的一个词了,看趋势自然离不开日期,年度趋势,月趋势,周趋势等等。Power BI中我们可以借助于计算表,计算组,字段参数来实现动态实时轴的效果。

计算表实现动态日期切换

这个方法在字段参数出来后是有些过时的,基本可以淘汰掉了,但是实现的思路还是可以借鉴的,这也涉及到了另一个主题,学习的本质,技术是一个在更新迭代的,但是一些思想是不变的。掌握了底层逻辑就能以不变应万变。

废话不多说,来回顾下具体怎么实现。

  • • 要实现动态切换日期轴,就要日期表中有一个类别,来标注当前的日期区间类别,是年,还是季度或者是月
  • • 不同的区间类型还应有一不同的区间名称,比如年区间类型时,该列应全是年份名称,为季度时,应是年季组合字段

就如下面这样,实际使用时还应加入相应的排序字段

区间区间类型日期
20232023-04-24
20232023-04-25
2023Q22023-04-24
2023Q22023-04-25

转换为计算表如下,代码有些长,其实类似的代码重复写了几遍而已

DatesPeriod = 
VAR DT = 'Dates'
VAR YearLevel =
    ADDCOLUMNS (
        SELECTCOLUMNS ( DT, "区间", "Y" & FORMAT ( [Year], "0" ), "日期", [Date] ),
        "区间类型", "年",
        "区间类型OrderBy", 1,
        "区间OrderBy", YEAR ( [日期] )
    )
VAR QuarterLevel =
    ADDCOLUMNS (
        SELECTCOLUMNS (
            DT,
            "区间",
                "Y" & FORMAT ( [Year], "0" ) & "Q"
                    & FORMAT ( [Quarter], "0" ),
            "日期", [Date]
        ),
        "区间类型", "季",
        "区间类型OrderBy", 2,
        "区间OrderBy", FORMAT ( [日期], "YYYY\QQ" )
    )
VAR MonthLevel =
    ADDCOLUMNS (
        SELECTCOLUMNS (
            DT,
            "区间",
                "Y" & FORMAT ( [Year], "0" ) & "M"
                    & FORMAT ( [Month], "0" ),
            "日期", [Date]
        ),
        "区间类型", "月",
        "区间类型OrderBy", 3,
        "区间OrderBy", FORMAT ( [日期], "YYYY\Mmm" )
    )

VAR DateLevel =
    ADDCOLUMNS (
        SELECTCOLUMNS ( DT, "区间", FORMAT ( [Date], "yyyy/M/dd" ), "日期", [Date] ),
        "区间类型", "日",
        "区间类型OrderBy", 4,
        "区间OrderBy", [日期]
    )
RETURN
    UNION ( YearLevel, QuarterLevel, MonthLevel,  DateLevel )

建立关系

效果如下

计算组实现动态日期切换

现在需求又变了,不需要按年季月这样来切换日期轴,而是根据参数的选择来切换,比如最近3个月,最近6个月,12个月的趋势。在计算组刚出来没多久sqlbi就给出了解决方案:

Show previous 6 months of data from single slicer selection – SQLBI — 显示来自单个切片器选择的前 6 个月的数据 – SQLBI[1]

  • • 新建参数,用于选择最近的日期
Period = GENERATESERIES(3, 36, 1)
  • • 需要复制一张日期表,和现有的日期表是虚拟关系
  • • 打开Tabular Editor新建计算组,这里只是修改了月份数为参数的值
VAR NumOfMonths = -[Months Value]
VAR ReferenceDate = MAX ( 'Dates'[Date] )
VAR Previous = DATESINPERIOD ( 'DatesPeriod2'[Date], ReferenceDate, NumOfMonths, MONTH )
VAR Result =
    CALCULATE (
        SELECTEDMEASURE (),
        REMOVEFILTERS ( 'Dates' ),
        KEEPFILTERS ( Previous ),
        USERELATIONSHIP ( 'DatesPeriod2'[Date], 'Dates'[Date] )
    )
RETURN Result

字段参数实现动态日期轴

字段参数真的是微软去年至今的更新中最好用的功能,比如日期轴实现动态的年季月切换,就不再需要写那么多代码来组合一张新表了,只需要在新建参数的时候选择我们需要的列即可。

接下来看下效果

细心的肯定已经发现了,图中x轴的顺序的错乱的,我们来手动修改下排序,当我们切换为其他类别时,再切换回来排序又乱了。。。

这是微软的bug,在去年时就已经有用户提交给微软了,一年快过去了。。。

这个bug修复起来,也相对简单,给我们字段参数中用到列都加一个排序列,比如年份列,可以复制一份然后对原始的年份列排序。

再来看下效果,排序正常了。

字段参数实现动态日期轴进阶

现在需求再进一步,时间轴要根据所选的时间范围自动切换,比如选择的范围大于36个月,就显示年趋势,范围小于30天就显示为日趋势。

实现起来也相对简单,只需要新建一个度量来判断就可以了,为了简单起见,这里对月的判断直接是除以30,如果筛选器是月的话,也可以直接统计月份数。

Flag = 
VAR Months = COUNT(Dates[Date])/30
VAR Periods = SELECTEDVALUE('动态日期轴 参数'[Order])
RETURN 
SWITCH(
    TRUE,
        Months <= 1 && Periods = 0, 1,
        Months > 1 && Months <= 3 && Periods = 1, 1,
        Months > 3 && Months <= 6 && Periods = 2, 1,
        Months > 6 && Months <= 24 && Periods = 3, 1,
        Months > 24 && Periods = 4, 1,
        0
    )

之后在图表上加一个筛选条件,是对字段参数中的排序列取top1

看下效果

总结

计算组和字段参数无疑是Power BI中最强大的两个功能,借助于这两个功能可以实现很多复杂的效果,只不过过程中还是需要注意是否还有一些小Bug,毕竟微软改起bug来真的很佛。

引用链接

[1] Show previous 6 months of data from single slicer selection – SQLBI — 显示来自单个切片器选择的前 6 个月的数据 – SQLBI: https://www.sqlbi.com/articles/show-previous-6-months-of-data-from-single-slicer-selection/

类似文章

发表回复

您的电子邮箱地址不会被公开。 必填项已用 * 标注