这样的索引列可以用DAX语言实现吗?

jsjcjsjc

这两列可以用DAX语言实现吗?
目前是excel中操作的~~


再补一张图方便理解
感谢关注


附件:
test 20220906.zip (50 KB, 下载次数: 1)

2022-9-6 17:03 上传

点击文件名下载附件

jsjcjsjc

这要是想用这段代码的时候,想把"索引"和"财年起始的索引号"一起带出来...方便

1. 0.通用日期表(香港4月) =
   
2. ADDCOLUMNS (
   
3.     CALENDAR (date(2015,01,1),date(2016,12,31) ),
   
4.     "年", YEAR ( [Date] ),
   
5.     "季度", QUARTER ( [Date] ),
   
6.     "月", MONTH ( [Date] ),
   
7.     "周", WEEKNUM ( [Date] ),
   
8.     "年季度", YEAR ( [Date] ) & "Q" & QUARTER ( [Date] ),
   
9.     "年月", YEAR ( [Date] ) * 100 + MONTH ( [Date] ),
   
10.     "年周", YEAR ( [Date] ) * 100 + WEEKNUM ( [Date] ),
    
11.     "星期几", WEEKDAY ( [Date] ),
    
12.     "财年FY", IF( MONTH([Date])<=3 , "FY"&YEAR([Date])-1 , "FY"&(YEAR([Date]) )),
    
13.     "财年", IF( MONTH([Date])<=3 , YEAR([Date])-1 , (YEAR([Date]) )),
    
14.     "财季", IF( QUARTER([Date])=1 , "Q4" , "Q"&QUARTER([Date])-1 ),
    
15.     "财月(排序用)", IF( MONTH([Date])<=3 , MONTH([Date])+9 , MONTH([Date])-3 ),
    
16.     "财年财月", IF( MONTH([Date])<=3 , YEAR([Date])-1 , (YEAR([Date]) )) &"-"& IF( MONTH([Date])<=3 , MONTH([Date])+9 , MONTH([Date])-3 )
    
17. )

复制代码

Eric0236

添加列
财年起始的索引号 = if('Sheet1'[索引]<4, 0, CALCULATE(MIN('Sheet1'[索引]), ALLEXCEPT('Sheet1', 'Sheet1'[财年])))

Eric0236

jsjcjsjc 发表于 2022-9-6 17:38
这要是想用这段代码的时候,想把"索引"和"财年起始的索引号"一起带出来...方便

0.通用日期表(香港4月) =
VAR start_ = date(2015,01,1)
VAR end_ = date(2016,12,31)
RETURN
    ADDCOLUMNS (
        CALENDAR (start_, end_ ),
        "年", YEAR ( [Date] ),
        "季度", QUARTER ( [Date] ),
        "月", MONTH ( [Date] ),
        "周", WEEKNUM ( [Date] ),
        "年季度", YEAR ( [Date] ) & "Q" & QUARTER ( [Date] ),
        "年月", YEAR ( [Date] ) * 100 + MONTH ( [Date] ),
        "年周", YEAR ( [Date] ) * 100 + WEEKNUM ( [Date] ),
        "星期几", WEEKDAY ( [Date] ),
        "财年FY", IF( MONTH([Date])<=3 , "FY"&YEAR([Date])-1 , "FY"&(YEAR([Date]) )),
        "财年", IF( MONTH([Date])<=3 , YEAR([Date])-1 , (YEAR([Date]) )),
        "财季", IF( QUARTER([Date])=1 , "Q4" , "Q"&QUARTER([Date])-1 ),
        "财月(排序用)", IF( MONTH([Date])<=3 , MONTH([Date])+9 , MONTH([Date])-3 ),
        "财年财月", IF( MONTH([Date])<=3 , YEAR([Date])-1 , (YEAR([Date]) )) &"-"& IF( MONTH([Date])<=3 , MONTH([Date])+9 , MONTH([Date])-3 ),
        "财年起始索引号", IF( MONTH([Date])<=3 && YEAR([Date])=YEAR(start_), 0, (IF( MONTH([Date])<=3 , YEAR([Date])-1 , (YEAR([Date]) ))-YEAR(start_))*12+4
    ))

jsjcjsjc

Eric0236 发表于 2022-9-7 11:00
添加列
财年起始的索引号 = if('Sheet1'[索引]

感谢老哥
但是我的[索引]这一列也没出来哈

jsjcjsjc

索引列还是搞不定哈

jsjcjsjc

目前的思路是如果当前行+1不等于当前行就加1，但是不知道如何实施

jsjcjsjc

Eric0236 发表于 2022-9-7 13:23
0.通用日期表(香港4月) =
VAR start_ = date(2015,01,1)
VAR end_ = date(2016,12,31)

老哥,里面的[索引]列有办法啊
目前的思路是如果当前行+1不等于当前行就加1，但是不知道如何实施