Powerbi实战--常用新建表代码(单位切换，日期表)

最新推荐文章于 2025-03-25 22:32:50 发布

咦，笔记存档

最新推荐文章于 2025-03-25 22:32:50 发布

阅读量1.8k

点赞数

文章标签： sql etl

本文链接：https://blog.youkuaiyun.com/weixin_41882890/article/details/123451874

版权

单位切换

#Model_Unit = DATATABLE("单位名称",STRING,"OrderBy_unit",INTEGER,"Value",INTEGER,    
    {
   
        {
    "无", 1, 1 },
        {
    "万", 2, 10000 },
        {
    "百万", 3, 1000000 },
        {
    "亿", 4, 100000000 }
    })

日期相关表

创建日期表

Model_日期表 = 
GENERATE (
CALENDARAUTO(),
VAR currdate = [Date]
VAR year = YEAR (currdate)
VAR quarter = VALUE( FORMAT( currdate, "Q" ) )
VAR quarter1 = "Q" & FORMAT( currdate, "Q" ) 
VAR month =MONTH( currdate )
VAR month1 =MONTH( currdate ) & "月"
VAR weeknum=WEEKNUM(currdate , 2 )
VAR weekday=WEEKDAY(currdate , 2 )
VAR weekname="星期" &RIGHT( FORMAT(currdate , "aaa" ) , 1 )
VAR mday=DAY(currdate )
VAR yday=VALUE( FORMAT(currdate , "y" ) )
VAR yq=year*10+quarter
VAR yq1=year&"Y"&quarter&"Q"
VAR yw=year*100+weeknum
VAR yw1=year&"Y"&weeknum&"W"
VAR ym=year*100+month
VAR ym1=year&"Y"&month&"M"
RETURN ROW (
"年度", year ,
"季序号",quarter,
"季名称",quarter1,
"月序号", month,
"月名称", month1,
"周序号", weeknum,
"周第几", weekday,
"星期几", weekname,
"年第几日", yday,
"月第几日", mday,
"年季序号", yq,
"年季名称", yq1,
"年月序号", ym,
"年月名称", ym1,
"年周序号", yw,
"年周名称", yw1
))

日期表

Model_Dates = 'Model_日期表'

创建日期区间表

Model_DatesPeriod = 
// 日期区间表，用于动态筛选日期

VAR X = 'Model_Dates' // 设置主日期表

VAR PeriodYearLevel = ADDCOLUMNS( SELECTCOLU

最低0.47元/天解锁文章