powerbi - 根据power bi中同一列中的上一行和另一列中的上一行计算

Question

我正在尝试在 power Bi 中计​​算 NoofEventsInQueue 列，如下所示。157是事件的总数

标签： powerbidaxpowerbi-desktop

Answer 1

在计算列中：

NumberOfEventsInQueue = 
IF ( 
    ISBLANK ( Table1[NoOfEventsAtATime] ),
    BLANK(),
    CALCULATE ( 
        SUM ( Table1[NoOfEventsAtATime] ),
        FILTER ( 
            ALL ( Table1 ),
            Table1[Ref_Time] >= EARLIER ( Table1[Ref_Time] )
        )
    )
)

如果您的NoOfEventsAtATime列中间有空白行，并且仍然希望在计算列上返回一个值，您可以使用类似：

NumberOfEventsInQueue = 
VAR TimeMin = 
    CALCULATE ( 
        MIN ( Table1[Ref_Time] ),
        FILTER ( 
            ALL ( Table1 ),
            NOT ISBLANK ( Table1[NoOfEventsAtATime] )
        )
    )
VAR TimeMax = 
    CALCULATE ( 
        MAX ( Table1[Ref_Time] ),
        FILTER ( 
            ALL ( Table1 ),
            NOT ISBLANK ( Table1[NoOfEventsAtATime] )
        )
    )
VAR Output = 
    IF ( 
        Table1[Ref_Time] >= TimeMin && Table1[Ref_Time] <= TimeMax,
        CALCULATE ( 
            SUM ( Table1[NoOfEventsAtATime] ),
            FILTER ( 
                ALL ( Table1 ),
                Table1[Ref_Time] >= EARLIER ( Table1[Ref_Time] )
            )
        ),
        BLANK()
    )
RETURN Output