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

最新推荐文章于 2024-10-09 13:52:41 发布

咦，笔记存档

最新推荐文章于 2024-10-09 13:52:41 发布

阅读量1.7k

点赞数

文章标签： sql etl

本文链接：https://blog.csdn.net/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( SELECTCOLUMNS( X , "区间" , "Y" & FORMAT( [年度] , "0" ) , "日期" , [Date] ) , "区间类型" , "年" , "区间类型OrderBy" , 10 , "区间OrderBy" , YEAR( [日期] ) )
VAR PeriodQuarterLevel = ADDCOLUMNS( SELECTCOLUMNS( X , "区间" , "Y" & FORMAT( [年度] , "0" ) & "Q" & FORMAT( [季序号] , "0" ) , "日期" , [Date] ) , "区间类型" , "季" , "区间类型OrderBy" , 20 , "区间OrderBy" , ( YEAR( [日期] ) * 10 + VALUE( FORMAT( [日期] , "q" ) ) ) * 10 )
VAR PeriodMonthLevel = ADDCOLUMNS( SELECTCOLUMNS( X , "区间" , "Y" & FORMAT( [年度] , "0" ) & "M" & FORMAT( [月序号] , "0" ) , "日期" , [Date] ) , "区间类型" , "月" , "区间类型OrderBy" , 30 , "区间OrderBy" , (  YEAR( [日期] ) * 100 + MONTH( [日期] ) ) * 100 )
VAR PeriodWeekLevel = ADDCOLUMNS( SELECTCOLUMNS( X , "区间" , "Y" & FORMAT( [年度] , "0" ) & "W" & FORMAT( [周序号] , "0" ) , "日期" , [Date] ) , "区间类型" , "周" , "区间类型OrderBy" , 40 , "区间OrderBy" , (  YEAR( [日期] ) * 100 + WEEKNUM( [日期] , 2 ) ) * 1000 )
VAR PeriodDateLevel = ADDCOLUMNS( SELECTCOLUMNS( X , "区间" , FORMAT( [Date] , "yyyy/M/dd" ) , "日期" , [Date] ) , "区间类型" , "日" , "区间类型OrderBy" , 50 , "区间OrderBy" , [日期] )
RETURN UNION( PeriodYearLevel , PeriodQuarterLevel , PeriodMonthLevel , PeriodWeekLevel , PeriodDateLevel )

日期计算表

Model_DatesPeriod.XTD = 
VAR vToday = TODAY()
VAR vLastUpdate = [Start:Date.LastDate.All]
VAR Period_Today = 
    VAR X   = TREATAS( { vToday } , 'Model_日期表'[Date] )
    VAR PX  = TREATAS( { vToday - 1 } , 'Model_日期表'[Date] )
    VAR TodayX = ADDCOLUMNS( LASTDATE( X ) , "区间类型" , "当日" , "截止到" , "最后一日" , "区间类型排序" , 1 )
    VAR TodayPX = ADDCOLUMNS( LASTDATE( PX ) , "区间类型" , "当日" , "截止到" , "上一日" , "区间类型排序" , 1 )
    VAR YesterdayX = ADDCOLUMNS( PREVIOUSDAY( X ) , "区间类型" , "上一日" , "截止到" , "最后一日" , "区间类型排序" , 2 )
    VAR YesterdayPX = ADDCOLUMNS( PREVIOUSDAY( PX ) , "区间类型" , "上一日" , "截止到" , "上一日" , "区间类型排序" , 2 )
    VAR WTDX = ADDCOLUMNS( DATESINPERIOD( 'Model_日期表'[Date] , X , - WEEKDAY( X , 2 ) + 1  , DAY ) , "区间类型" , "WTD" , "截止到" , "最后一日" , "区间类型排序" , 3 )
    VAR WTDPX = ADDCOLUMNS( DATESINPERIOD( 'Model_日期表'[Date] , PX , - WEEKDAY( PX , 2 ) + 1  , DAY ) , "区间类型" , "WTD" , "截止到" , "上一日" , "区间类型排序" , 3 )
    VAR MTDX = ADDCOLUMNS( DATESMTD( X ) , "区间类型" , "MTD" , "截止到" , "最后一日" , "区间类型排序" , 4 )
    VAR MTDPX = ADDCOLUMNS( DATESMTD( PX ) , "区间类型" , "MTD" , "截止到" , "上一日" , "区间类型排序" , 4 )
    VAR QTDX = ADDCOLUMNS( DATESQTD( X ) , "区间类型" , "QTD" , "截止到" , "最后一日" , "区间类型排序" , 5 )
    VAR QTDPX = ADDCOLUMNS( DATESQTD( PX ) , "区间类型" , "QTD" , "截止到" , "上一日" , "区间类型排序" , 5 )
    VAR YTDX = ADDCOLUMNS( DATESYTD( X ) , "区间类型" , "YTD" , "截止到" , "最后一日" , "区间类型排序" , 6 )
    VAR YTDPX = ADDCOLUMNS( DATESYTD( PX ) , "区间类型" , "YTD" , "截止到" , "上一日" , "区间类型排序" , 6 )
    RETURN UNION( TodayX , TodayPX , YesterdayX , YesterdayPX , WTDX , WTDPX , MTDX , MTDPX , QTDX , QTDPX , YTDX , YTDPX )
VAR Period_LastUpdate = 
    VAR X   = TREATAS( { vLastUpdate } , 'Model_日期表'[Date] )
    VAR PX  = TREATAS( { vLastUpdate - 1 } , 'Model_日期表'[Date] )
    VAR TodayX = ADDCOLUMNS( LASTDATE( X ) , "区间类型" , "当日" , "截止到" , "最后一日" , "区间类型排序" , 1 )
    VAR TodayPX = ADDCOLUMNS( LASTDATE( PX ) , "区间类型" , "当日" , "截止到" , "上一日" , "区间类型排序" , 1 )
    VAR YesterdayX = ADDCOLUMNS( PREVIOUSDAY( X ) , "区间类型" , "上一日" , "截止到" , "最后一日" , "区间类型排序" , 2 )
    VAR YesterdayPX = ADDCOLUMNS( PREVIOUSDAY( PX ) , "区间类型" , "上一日" , "截止到" , "上一日" , "区间类型排序" , 2 )
    VAR WTDX = ADDCOLUMNS( DATESINPERIOD( 'Model_日期表'[Date] , X , - WEEKDAY( X , 2 ) + 1  , DAY ) , "区间类型" , "WTD" , "截止到" , "最后一日" , "区间类型排序" , 3 )
    VAR WTDPX = ADDCOLUMNS( DATESINPERIOD( 'Model_日期表'[Date] , PX , - WEEKDAY( PX , 2 ) + 1  , DAY ) , "区间类型" , "WTD" , "截止到" , "上一日" , "区间类型排序" , 3 )
    VAR MTDX = ADDCOLUMNS( DATESMTD( X ) , "区间类型" , "MTD" , "截止到" , "最后一日" , "区间类型排序" , 4 )
    VAR MTDPX = ADDCOLUMNS( DATESMTD( PX ) , "区间类型" , "MTD" , "截止到" , "上一日" , "区间类型排序" , 4 )
    VAR QTDX = ADDCOLUMNS( DATESQTD( X ) , "区间类型" , "QTD" , "截止到" , "最后一日" , "区间类型排序" , 5 )
    VAR QTDPX = ADDCOLUMNS( DATESQTD( PX ) , "区间类型" , "QTD" , "截止到" , "上一日" , "区间类型排序" , 5 )
    VAR YTDX = ADDCOLUMNS( DATESYTD( X ) , "区间类型" , "YTD" , "截止到" , "最后一日" , "区间类型排序" , 6 )
    VAR YTDPX = ADDCOLUMNS( DATESYTD( PX ) , "区间类型" , "YTD" , "截止到" , "上一日" , "区间类型排序" , 6 )
    RETURN UNION( TodayX , TodayPX , YesterdayX , YesterdayPX , WTDX , WTDPX , MTDX , MTDPX , QTDX , QTDPX , YTDX , YTDPX )
RETURN 
    UNION( 
        ADDCOLUMNS( Period_Today , "相对日期" , "现实日期" ),
        ADDCOLUMNS( Period_LastUpdate , "相对日期" , "刷新日期" )
    )